This may be more of a query problem, because I'm still getting used to using forms to update data and in Access I am only accustomed to the select queries, but less used to building insert/append queries in Access.
What I want to do is this: while entering a new customer in the DB, I have a query for the customer's address. The idea is that if the address is already in the DB, we simply use that address rather than create a new record for the address table. If the address comes up in the form, (I'll call it FORM #1) then I have a button with the option of using the current address in the customer's new record (CALL IT FORM #2). Ideally, the new customer record entry form (#2) would come up with the address information already present (from #1). Then I would just fill in the customer info and the transaction info. I've tried different queries to make this work. The main thing I'm doing is trying to retrieve the one address by making sure the addressID matches the one in form#1. In the macro which opens form#2, I have a simple WHERE clause, something like this...WHERE TblAddress.AddressID = [Forms]![Form#1]![AddressID]
I've also tried putting this WHERE clause in the actual query for form#2, but that doesn't work either. The problem is that, depending on the query, I get different results. Either I get no results in Form#2, or I get too many records of different customers ALREADY in DB, rather than blank fields to enter a new cust. Again, maybe I need to use an update or append query, but I'm really not sure how to work this into my form. In the macro, I've also tested opening form #2 both in Edit and Add mode, so its not that. I hope this makes some sense and that someone can help.
What I want to do is this: while entering a new customer in the DB, I have a query for the customer's address. The idea is that if the address is already in the DB, we simply use that address rather than create a new record for the address table. If the address comes up in the form, (I'll call it FORM #1) then I have a button with the option of using the current address in the customer's new record (CALL IT FORM #2). Ideally, the new customer record entry form (#2) would come up with the address information already present (from #1). Then I would just fill in the customer info and the transaction info. I've tried different queries to make this work. The main thing I'm doing is trying to retrieve the one address by making sure the addressID matches the one in form#1. In the macro which opens form#2, I have a simple WHERE clause, something like this...WHERE TblAddress.AddressID = [Forms]![Form#1]![AddressID]
I've also tried putting this WHERE clause in the actual query for form#2, but that doesn't work either. The problem is that, depending on the query, I get different results. Either I get no results in Form#2, or I get too many records of different customers ALREADY in DB, rather than blank fields to enter a new cust. Again, maybe I need to use an update or append query, but I'm really not sure how to work this into my form. In the macro, I've also tested opening form #2 both in Edit and Add mode, so its not that. I hope this makes some sense and that someone can help.
