Still got requery problem

[Bentleybelle]

I've been trying to apply a requery, but still can't get desired result. My db structure: I have an input form with 4 tabbed pages. First page of form is for entering basic details of a client. Second page goes into more detail. At top of second page, I have converted text box into a combo box and through query builder all client names appear within combo box in alpha order, so user does not have to retype client names. Trouble is the client name does not appear in combo box unless I close and re-open the form. Rickster57 kindly gave me code for a requery of:

Forms![first form]![second form].Form![combo box name].Requery

Have put in the After Update property of the client name text box on page 1

I question now if it's a form I'm working with or pages? (You'll understand I'm relatively new to VBA!!!)

Any help appreciated

 

[RuralGuy]

How about trying this: Me.ComboBoxName.Requery in the GotFocus event of the ComboBox? Using your ComboBoxName of course!

 

[Bentleybelle]

Well, I'm a bit closer - first time I click on page 2, it is not in the combo box list, if I click back to page 1 tab, then page 2 again, it's there! So looks to me it's doing the requery, but not at the right time(?) Any ideas?

 

[RuralGuy]

If you put the requery in the GotFocus event as I suggested and the data was not present yet then the problem is you have not saved the record yet. Examine that code more closely.

 

[Bentleybelle]

I put a "Save" command button on the first page, because I figured the record had not been saved, but this makes no difference. Sorry, not sure what you mean by examine the code, it is against the GotFocus property.

 

[RuralGuy]

Sorry but you may need to post your db sans any confidential information so we can see what is going on. Make sure there is enough sample data to demonstrate the problem.

 

[Bentleybelle]

Thanks for your help so far. I have attached db. I have had to delete live data and one or two tables to get the size down. Please look aat frmMemberDetails. On page 1 (Member Details) the user completes basic data, then clicks to page 2 (Addresses) I want the company name to drop down from the combo box so the user does not have to input this info again. Since I have deleted the live data, this bit is not working at all. If you could help me sort this, I would be so grateful! Been working on this now for about 3 days!!

 

[RuralGuy]

You are probably not going to appreciate the answer but your Addresses table should *not* be duplicating the CompanyName anyway. That is a normalization violation. I'll offer a few suggestions:
When you put a PrimaryKey (PK) in a table as a ForeignKey (FK), use the same name for the field as you did in the table where it is a PK. It is much easier to understand and follow. Do you really need user visible MRN's? I would recommend using AutoNumber fields as your PK's and not show them to the user. Let Access manage the PK and FK fields for you. It actually does a pretty good job of it. Let the user look things up by the names they know but save the Key in your tables.

 

[Bentleybelle]

Thanks Rural Guy. I actually do appreciate your comments!! And have taken them on board. And to think I have spent so much time on this! I have learned quite a lot from this litle exercise, and I'm sure I'll be back! Many thanks.

 

[RuralGuy]

Learning is what these forums are all about. Please feel free to post whatever question you might have whenever you want. There is almost always someone around that can provide a gentle nudge in the right direction. We all started somewhere.

 

[rhett7660]

Ruralguy..

Thank you for the requery on the getfocus, worked like a charm for me!

 

[RuralGuy]

You're very welcome.