Combo box updates first record not current

[Lisa]

Hello -

I have two combo boxes on a tabbed form (the fact that it's tabbed is
probably irrelevant but just thought I'd throw that in) that populate a
subform. That part works great except that the update isn't on the current
record. It always goes back to the first record and updates that one.

Any ideas? Thanks.

Lisa

 

[KARL DEWEY]

Open form on tab that has the combo in design view, click on the combo,
double click, scroll down and select Properties.
What is the combo bound to?

 

[Lisa]

There are two copies of the same combo box, because I need two different
records from the same table (it's a table called "Agents"). One combo box
lets the user pick the Buyer's Agent and the other the Seller's Agent (but
again, both combos reference the Agents table).

The Control source for the first combo is called BuyerAgentID and the second
SellerAgentID. These two fields are in the Jobs table. Clients and Jobs are
related tables.

Hope this helps. Thanks much.

 

[KARL DEWEY]

Sorry, I cannot help.

 

[Charles Wang [MSFT]]

Hi Lisa,
Could you please send me (changliw_at_microsoft_dot_com) a piece of your
test Access database so that I can easily reproduce your issue and perform
effective research?

In this response, I would like to give you some general suggestions on
updating a record in access. Hope they are helpful.
1. Use RunSQL method
For example:
=============================
Dim strSQL As String

strSQL = "UPDATE MyTable SET MyField = 1234 WHERE IDField = 1"

' Use RunSQL to execute the statement.
' This will result in a user confirmation dialog.
DoCmd.RunSQL strSQL

' Use DAO to execute the statement -- no dialog.
CurrentDb.Execute strSQL, dbFailOnError
===============================

2. Use Recordset object
Working with recordsets is more object-oriented, though may be less
efficient than just executing SQL statements.
For example:
=============================================
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb

' Read a value from a specific record, ID known in advance:
Set rst = db.OpenRecordset( "SELECT MyField FROM MyTable WHERE IDField =
1")

' Open a recordset and loop through records, editing some of them:
Set rst = db.OpenRecordset("MyTable")
With rst
' Let's find a particular record.
.FindFirst "IDField = 1"
!MyField = 1234
.Update

' Always close the recordsets you open.
.Close

End With
============================================

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Charles Wang [MSFT]]

Dear Lisa,
Sorry for letting you wait.

I just come back from my vacation. Regarding this issue you encountered,
based on my research today, you can resolve it by removing the embedded
macro for your BuyerAgent combo box's After Update event, instead, use an
event procedure. Here is the code in the Event Procedure:
Private Sub Combo309_AfterUpdate()
Child321.LinkMasterFields = "BuyerAgentID"
Child321.LinkChildFields = "AgentID"
Child321.SourceObject = "Agents Entry Form"
Child321.Requery
End Sub

You can apply similar code for your Seller Agent combo box:
Private Sub Combo311_AfterUpdate()
Child330.LinkMasterFields = "SellerAgentID"
Child330.LinkChildFields = "AgentID"
Child330.SourceObject = "Agents Entry Form"
Child330.Requery
End Sub

Besides, I notice that there are also some code issue in the "On Current"
event of your form "Final Inspection Form". Originally you code it as the
following:
Private Sub Form_Current()
Combo309 = ClientID
Combo311 = ClientID
End Sub

Based on my understanding, combo309 should refer to BuyerAgentID and
combo311 should refer to SellerAgentID instead of ClientID. I change your
code to the following:
Private Sub Form_Current()
Combo309 = BuyerAgentID
Combo311 = SellerAgentID
End Sub

I have tested the above codes at my side. They work fine at my side and
hope they work at your side as well. I also attach the changed version file
in this email for your reference.

Thanks.

Best regards,
Charles Wang