I have two tables that are used in a single form. I have no problems with the backend tables working correctly. However the forms are causing me a headache and a half.
The form I need to design depends on two tables:
Address_ID [FK referencing the Address Table]
Currently my team and I decided to go this way due to the fact that multiple people have a joint membership or joint ownership of a dog rescued from our client. This way more than one person can have the same address, and searching can be accomplished using an address and returning all the members names and forms. It also can not be changed due to the parallel development of other forms and reports.
However, when entering people on the form (which shows all of the above fields minus ID and MemberID), it will allow duplicate addresses to be entered. What I need is when someone begins to type an address, it autocompletes the address with the first possible match -similiar to the Address box in an internet browser. Once focus is lost from the address field or Enter is pressed, if the address matches an existing one the remaining fields from the address table are filled in automatically and the Address_ID field is updated to match the ID from the address table. Or in the event it is a new address, the fields remain blank for continued data entry.
Any ideas on what the code should be?
All I can think of is using the keyPress event, the OnEnter event, or a focus event. But I have no idea how to proceeed.
You would need to use the keypress event. How you could do this is to pull all the current addresses that are available to match into an array (or local table) and on each keypress query for a LIKE matching address and if there is a match display it ... I would almost rather put up a combobox with the addresses in it and let the user type away... Then upon the lostfocus event you could retrieve the corresponding CSZ for the address if it was in the list.
I used a combo box as you suggested: The code I used ended up having an endless loop. I used a breakpoint to determine the problem and found that when I clicked on the next form it repeated the code, so I could never continue data entry.
However, currently my code looks like this:
Private Sub cboAddress_AfterUpdate()
Me!Address_ID = Me!cboAddress.Column(0)
Private Sub cboAddress_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
I plan to add a DoubleClick event to open a form for entering a new address if it is not in the list, as I have limited the list for Addresses to existing addresses.
Address_ID refers to the FK in the Members Table.
I can manually change the Address_ID but it will not pass the Address_ID from the Address Combo box to the field. I get a field can not be updated error.
What way do I need to change the AfterUpdate code to allow for dynamically chaging that field?
Also, I plan to have an AfterUpdate code to requery the address table for the rest of the fields. How would I code that?
Re: I decided to attach the partial db to this thread
Originally posted by mom2twinzz
I attached a prototype of the database tables and forms that are related to the member table. I only copied the structure to protect confidential data. Any help in the code is greatly appreciated.
Have you tried basing your form on an AutoLookup query? It comes out as a Lookup box and when you select your entry, it fills in the other fields that you setup in the query. It's on a one-to-many relationship. It goes like this: 2 tables, Customers, Orders. Both have primary keys. I have the CustomerID in the Orders table as a ForeignKey. Now, in the query take the CustomerID fk from the Orders table(the many side) and whatever other fields you will want to be filled in from the Customers table(the one side). It works for me and I also use it to fill in shipping addresses and what not. Try it if you like.