Strange form problem

[Extinct]

Hi,

This should be quite simple.

I have 1 master table and several linked tables without enforced referential integrity.

I made a form that shows a record from the main table based on a selection in a combobox (query from main table) and several subforms for the linked tabled.
The main form shows the combobox and 3 additional fields as textboxes.
I have made a procedure in the noInList event of the combobox to add records to the main table.
Here is the code for the afterupdate and notinlist events.

Private Sub Packet_Id_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Packet_Id] = '" & Me![Packet_Id] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Packet_Id_NotInList(NewData As String, Response As Integer)
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPackets", dbOpenDynaset)
rst.AddNew
rst!Packet_Id = NewData
rst.Update
rst.Close
Response = acDataErrAdded
End Sub

Now when I enter a value in the combobox that doesn't exist the record is added to the main table.
This works but the form then shows the new value in the combobox and the other textboxes (based on the main table) are filled in with values from other records. They should be empty. Even when I select another record with the combobox and then switch back to the new value the textboxes will still be filled in with data from another record.
However in the main table only the first field is filled in. and when I close the form and reopen the new record shows the correct values (i.e. empty textboxes)

Anyone any ideas?

 

[markgrizzle]

Try requerying the form's recordsource after adding the new record.

Not sure why it's mismatching the records, but you might also look at Tools | Options | Advanced tab, and modify the Refresh Interval setting.

Shorter settings will increase network traffic, so you'll have to strike a compromise f this is in fact the culprit.

 

[Extinct]

Still the same problem and I have tried everything recalc, requery, refresh, repaint of the form requery of the textboxes. refresh in the records menu. Nothing works. Can somebody help me???

 

[MakeItSo]

Guess you must requery all textboxes.
Write a little procedure like

Sub Requery_All()
either
textbox1.requery
textbox2.requery
or
docmd.requery textbox1
...

Looks rather odd, but it should work. Then you can call this from your Packet_Id_AfterUpdate

If this does not work, then something is wrong with your restriction query for the other textboxes...

Cheers,
Andy