I have a problem that is plaguing me at the moment.

I have a simple form which contains one combo box that selects a value
for the "PooledID" field from a query and a textbox that give the
value of the PooledID.

This works OK the first time I use it for a given PooledID. But,
after I close this form with a Cancel command button, it puts in the
value of PooledID into the last record in the table. Why? I have
commented out the VBA code and it still does the same thing when I
close the form.The problem is associated with the form, its controls
and closing it. (The next time I select that same PooledID, it finds
one record with the DCount() command.

The Record Source for the form is

SELECT Samples.*, Samples.AssayID, Samples.SampleNumber FROM Samples
ORDERBY Samples.AssayID DESC , Samples.SampleNumber DESC;

The query for the cbo box is

SELECT qryTotalPooledData.PooledID, [PooledName] & ", " & [Category]
& ", " & [ZoneName] AS List, qryTotalPooledData.PooledName,
qryTotalPooledData.Category,
qryTotalPooledData.ZoneName,
qryTotalPooledData.PooledAccountableAreaFROM qryTotalPooledDataORDER
BY qryTotalPooledData.ZoneName, qryTotalPooledData.Category,
qryTotalPooledData.PooledName;

The VBA code for the command "Get Group results" is

Dim strMsg As String
Dim intNoRecords As Variant
Dim intPooledID As Integer
Dim intResponse As Integer
Dim SQL As String

' Get the value for the PooledID
intPooledID = Me.txtPooledID.Value
' Be sure there are records in the "Samples" table with that value for
the Pooled ID
intNoRecords = DCount("[ID]", "Samples", "[PooledID] =" & intPooledID)

" If there are none give a message then exit
If intNoRecords = 0 Then
intResponse = MsgBox("The Group with ID number " & intPooledID & _
" has no Samples associated with it!", _
vbOKOnly)

Exit Sub

End If

' If there are records, then do the analysis and print the results
DoCmd.OpenReport "rptGroupBioburdenCalculationPlusONEAssay",
acViewPreview

End Sub


Anybody have any idea why this changes the value of PooledID in the
record? Is it a glitch in Access2007? More likely I am doing something
stupid. I have only been at this for a year or so.

Bob B.

Bob B. wrote:


It sounds like the combo box is bound to a field in the
table. You should use a separate combo box or some other
mechanism to select the data for the report and leave the
data entry combo box to do it's primary job,


--
Marsh

On Feb 28, 3:08*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
Marsh,

You were 100% correct. I have left the record source for the form,
control source for the combo box blank, and the control source for the
text box blank and all works. The only item necessary is the value of
the text box which is the criteria for the query to which the report
is bound.

What I still don't understand is that the text box still puts in the
PooledID value even though its control source is blank and unbounded.
How does it know?

I still have a lot to learn here!

Bob B.

Bob B. wrote:

On Mar 1, 7:39*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:

Marsh,
I found the reason: I did have VBA code for the cboAfter Update
procedure that puts the value in the text box.

I had done that while I was trying various things. and had forgotten
of course.

Thanks. I learned something today.

Bob B.