Main form and subforms not displaying correct information for new records (1 Viewer)

coryamber · Feb 13, 2018

I have a lookup combo box in a main form with the following VBA AfterUpdate:

'Clone the form's table/query into a recordset.
Dim MyRecSet As Object
Set MyRecSet = Me.Recordset.Clone

'Find first matching record in the recordset.
MyRecSet.FindFirst "[CasePlanID] = " & Me![Lookup]

'Set the form's record to found record.
Me.Bookmark = MyRecSet.Bookmark

I need the data selected in this lookup combo box to populate fields on the main form (where the lookup combo is) and in several subforms. This was working perfectly until I added a new record. Now, it still works perfectly for all existing records but when I select the newly added record, the form and subforms populate with data for the very first record ever entered instead of the new one. Anyone have any suggestions or run into this before? I have been troubleshooting all day and am all out of ideas. Any help is greatly appreciated!

isladogs · Feb 13, 2018

Try

Code:

Dim MyRecSet As DAO.Recordset

and assuming Lookup is a number field

Code:

MyRecSet.FindFirst "CasePlanID = " & Me.Lookup

If its text then

Code:

MyRecSet.FindFirst "CasePlanID = '" & Me.Lookup & "'"

coryamber · Feb 13, 2018

Thanks for responding. I tried that and I'm still having the same issue... any other ideas?

isladogs · Feb 13, 2018

Not offhand. Suggest you post your db for someone to look at.
As you have less than 10 posts, you need to zip it.
See FAQ section for instructions

arnelgp · Feb 13, 2018

is the combo bound?

you must need to tell access to add new record when it did not find the CasePlanId:

Dim bkMark As Variant
'Find first matching record in the recordset.
MyRecSet.FindFirst "[CasePlanID] = " & Me![Lookup]

If Not .MyRecSet.NoMatch Then
'Set the form's record to found record.
bkMark = MyRecSet.Bookmark
End If
Set MyRecSet=Nothing
If IsEmpty(bkMark) Then
If MsgBox("CasePlanID " & Me!Lookup & " is not in the table. Do you want to create new record?",vbQuestion+vbYesNo) = vbYes Then
DoCmd.GoToRecord,,acNewRec
Me.CasePlanID = Me!Lookup
End If
Else
Me.Bookmark = bkMark
End If

coryamber · Feb 13, 2018

The combo is unbound. The new record is being added and the caseplanID is generated... the new record shows up in the tables and it shows up in the combo box. However, when you select it in the combo box, the information displayed on the main form and subforms are for the first record ever entered instead of the new record that was just added. When you select any other record in the combo box, the information displayed is perfectly accurate. It's just not displaying the right information for the newest record added.

arnelgp · Feb 13, 2018

if that is the case add sort (desc - descending) to your recordsource.

coryamber · Feb 13, 2018

I tried that, still didn't work.

arnelgp · Feb 13, 2018

after adding the record (AfterUpdate event of Form), requery your combo.

Main form and subforms not displaying correct information for new records (1 Viewer)

coryamber

New member

isladogs

MVP / VIP

coryamber

New member

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

coryamber

New member

arnelgp

..forever waiting... waiting for jellybean!

coryamber

New member

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread