record not available in subform after table update

[drStealth]

All, OK got another form/subform question today.

In a form/subform structure I have a combo box in the subform which loads relevant values for a particular selection. Example, I'm working on an inventory database. In the subform the user can select from available model numbers and auto-populate relevant fields (hardware type, serial number, owner, manufacturer, etc).

I created a separate form ("new items form&quot for data entry into the same item table in case that a particular item is not currently in the database. There is a button available which (on click) opens the new items form. Related data is correct in the item table when new items are added.

But, if I try to access the new data from the subform drop down, it does not immediately appear. I have to exit and re-enter for the data to be available. I need the new data available while the form/subform are open.

Any help is appreciated as always. Thanks to all.
drStealth

 

[Norris68]

You will have to Requery your form.

1. Take note of your current record e.g. PK
2. Call Requery method of form(s)
3. Use the Find method of the form recordset to reposition you back to where you were (PK)
e.g.
Dim RecID As Long
RecID = me.txtRecID
Me.Requery
Me.Recordset.Find "RecID=" & RecID

NB: You will need to either use Find or Findfirst method depending whether you are using ADO or DAO. Above example is ADO. DAO will take extra code, and you call the FindFirst method of the RecordsetClone object, then set the Recordset.Bookmark equal to the RecordsetClone.Bookmark.

 

[drStealth]

Norris68,

This makes logical sense to me but the requery is not working. The data is in the table, I can see it immediately after it is updated.

The combo box that needs to read this "refreshed" data is in the subform. If I exit and re-enter the database then it is available. Otherwise it is not.

I'm sure it's a syntax issue, but curiously the requery itself isn't doing what I would expect it to do.

Any thoughts from anyone? drStealth

 

[drStealth]

GOT IT

Used a command button inside the subform. On click it refreshes the form. The data is now available in the combo box.

Not ideal (I think they prefer this to happen without another button on the form) but it works.

Thanks to all. drStealth

 

[Norris68]

You should be able to access it via VBA. Something like:

Me.Subform.Form.Requery

where 'Subform' is the name of the subform object control on your main form - not the form contained within.

Making ...

Dim ID As Long
ID = Me.Subform.Form.txtID
With Me.Subform.Form
.Requery
.Recordset.Find "ID=" & ID
End With