Bound Form will only update 1st record

[Guest]

I have a form bound to a single table, with 2 combo boxes. The first combo
box uses a simple query based on the same table as it's row source (uses
SELECT DISTINCT). After a selection is made in the 1st combo box, a query is
run and the second comboBox rowsource is based on the results of that query.
All this works fine and the correct records are displayed in the form and
subform. The problem comes when I try to edit data on the form. No matter
what record I'm on when I make the edits, the edits are always saved to the
first record, and any further edits will just continue to overwrite the data
for the first record. I can navigate to the correct records by using the
combo boxes, but why will it not save data to the record I'm on when I make
the edits???

I've read that a query that uses the DISTINCT keyword is not updateable- but
this query is only used as a row source for one of the comboBoxes, the Form
is actually bound to a single table- could this Non updateable query be
what's causing the problem in updating the table?

I think I recall having this problem once before on a database that was very
similar to this one, and I spent weeks reading these discussion boards before
I finally found a solution. Now I can't remember what in the world it was to
save my life, and in the last few days I've not been able to find anything
similar to this in the discussion boards. Does anyone have any ideas??? The
AfterUpdate code for the comboBoxes as well as my cmdSaveChanges code is
listed below:


Private Sub cboTestDescription_AfterUpdate()
DoCmd.OpenQuery "qryTestLab", acViewNormal, acReadOnly
If "qryTestLab" = "" Then
Me.cboLabName.RowSource = "Select tblOutsideLabListing.[Lab Name]
FROM [tblOutsideLablisting]" _
& "ORDER BY tblOutsideLabListing.[Lab Name]"
End If
DoCmd.Close acQuery, "qryTestLab"
cboLabName.Requery
End Sub

Private Sub cboLabName_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Test Description] &[Lab Name] = '" &
Me![cboTestDescription] & Me![cboLabName] & "'"
Me.Bookmark = rs.Bookmark
Me.[Lab Name] = Me.cboLabName
fsubOutsideLabListing.Visible = True
Me.Refresh
fsubOutsideLabListing.Requery
End Sub

Private Sub cmdSaveChanges_Click()
On Error GoTo Err_cmdSaveChanges_Click
Me.txtLastUpdate = Date
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdSaveChanges_Click:
Exit Sub
Err_cmdSaveChanges_Click:
MsgBox Err.Description
Resume Exit_cmdSaveChanges_Click
End Sub

Thank you for any assistance

 

[John Vinson]

I have a form bound to a single table, with 2 combo boxes. The first combo
box uses a simple query based on the same table as it's row source (uses
SELECT DISTINCT). After a selection is made in the 1st combo box, a query is
run and the second comboBox rowsource is based on the results of that query.
All this works fine and the correct records are displayed in the form and
subform. The problem comes when I try to edit data on the form. No matter
what record I'm on when I make the edits, the edits are always saved to the
first record, and any further edits will just continue to overwrite the data
for the first record.

I think the problem is in the code you're using to navigate to the new
record. You're concatenating things incorrectly, I believe! Try
changing the line

rs.FindFirst "[Test Description] &[Lab Name] = '" &
Me![cboTestDescription] & Me![cboLabName] & "'"


to

rs.FindFirst "[Test Description] = '" & = '" &
Me![cboTestDescription] & " AND [Lab Name] = '" & Me![cboLabName] &
"'"

and (for safety) add:

If rs.NoMatch Then
MsgBox "This test in this lab was not found"
Else
Me.Bookmark = rs.Bookmark
End If



John W. Vinson[MVP]

 

[Guest]

Hi John,

The code you posted would not compile, I've tried changing things around a
few different ways, and have gotten Syntax Error(missing Operator) in
expression, and data type mismatch error. Both of the combo boxes are text
type data. Any ideas about a slight rearrangement of that code?

The code I've been using has worked for me in the past with two combo boxes.
My first logical thinking was that they should be concatenated the way that
you suggested, but a while back when I first started working with access,
someone else on these boards suggested it to me that way and it worked, so
that's the way I've been doing it.

I don't understand why it works for displaying the correct record on the
form and subform, but doesn't save my edits???

I have a form bound to a single table, with 2 combo boxes. The first combo
box uses a simple query based on the same table as it's row source (uses
SELECT DISTINCT). After a selection is made in the 1st combo box, a query is
run and the second comboBox rowsource is based on the results of that query.
All this works fine and the correct records are displayed in the form and
subform. The problem comes when I try to edit data on the form. No matter
what record I'm on when I make the edits, the edits are always saved to the
first record, and any further edits will just continue to overwrite the data
for the first record.

I think the problem is in the code you're using to navigate to the new
record. You're concatenating things incorrectly, I believe! Try
changing the line

rs.FindFirst "[Test Description] &[Lab Name] = '" &
Me![cboTestDescription] & Me![cboLabName] & "'"


to

rs.FindFirst "[Test Description] = '" & = '" &
Me![cboTestDescription] & " AND [Lab Name] = '" & Me![cboLabName] &
"'"

and (for safety) add:

If rs.NoMatch Then
MsgBox "This test in this lab was not found"
Else
Me.Bookmark = rs.Bookmark
End If



John W. Vinson[MVP]

 

[Guest]

I thought i would post a few more details and see if anyone can help. This
is the code that gives me the data type mismatch error. Both combo boxes are
text data type. Can anyone tell me what's wrong with this code?

rs.FindFirst "[Test Description] = '" & Me![cboTestDescription] & "'"
And "[Lab Name] = '" & Me![cboLabName] & "'"

Some of the tests in the table have a default Lab assigned to them, but many
others do not. I run a query after the test is selected to determine if the
test has a default Lab. If it does, then the default lab name shows up in
the combo box, if not, then a full List of all Labs displays in the combo box
to select from. Once a user selects a Lab I want it to save that particular
Lab as the default for the current Test selected in the first combo box
(along with other test information as entered). I'm getting everything to
display correctly, it just won't save to my table. Any Ideas???

Hi John,

The code you posted would not compile, I've tried changing things around a
few different ways, and have gotten Syntax Error(missing Operator) in
expression, and data type mismatch error. Both of the combo boxes are text
type data. Any ideas about a slight rearrangement of that code?

The code I've been using has worked for me in the past with two combo boxes.
My first logical thinking was that they should be concatenated the way that
you suggested, but a while back when I first started working with access,
someone else on these boards suggested it to me that way and it worked, so
that's the way I've been doing it.

I don't understand why it works for displaying the correct record on the
form and subform, but doesn't save my edits???

I have a form bound to a single table, with 2 combo boxes. The first combo
box uses a simple query based on the same table as it's row source (uses
SELECT DISTINCT). After a selection is made in the 1st combo box, a query is
run and the second comboBox rowsource is based on the results of that query.
All this works fine and the correct records are displayed in the form and
subform. The problem comes when I try to edit data on the form. No matter
what record I'm on when I make the edits, the edits are always saved to the
first record, and any further edits will just continue to overwrite the data
for the first record.

I think the problem is in the code you're using to navigate to the new
record. You're concatenating things incorrectly, I believe! Try
changing the line

rs.FindFirst "[Test Description] &[Lab Name] = '" &
Me![cboTestDescription] & Me![cboLabName] & "'"


to

rs.FindFirst "[Test Description] = '" & = '" &
Me![cboTestDescription] & " AND [Lab Name] = '" & Me![cboLabName] &
"'"

and (for safety) add:

If rs.NoMatch Then
MsgBox "This test in this lab was not found"
Else
Me.Bookmark = rs.Bookmark
End If



John W. Vinson[MVP]

 

[John Vinson]

I thought i would post a few more details and see if anyone can help. This
is the code that gives me the data type mismatch error. Both combo boxes are
text data type. Can anyone tell me what's wrong with this code?

rs.FindFirst "[Test Description] = '" & Me![cboTestDescription] & "'"
And "[Lab Name] = '" & Me![cboLabName] & "'"

Yes. The word AND is outside the quotes. What you want to do is build
up a string, a valid SQL WHERE clause, in this case looking something
like

[Test Description] = 'Elemental Analysis' AND [LabName] = 'Argonne'

To build this string you'll intermix string constants such as

"[Test Description] = '"

with variable references such as Me![cboLabName].

Change the code to

rs.FindFirst "[Test Description] = '" & Me![cboTestDescription] &
"' And [Lab Name] = '" & Me![cboLabName] & "'"

and you should be ok (provided that both fields and both combo boxes
are Text values).

John W. Vinson[MVP]