I am building a database for a temp employer in Access 2003. DB is for keeping track of contacts/vehicles. The two main tables are People & Vehicles. I need to be able to open a form (Add Contact Form, already built) to add new 'people' (to the People table) and then click on a button opening a new form (AddVehicles) to add vehicles to the vehicle table. Both tables have a primary key called "ID#" which is identical for the associated records on each side.
I need to be able to have a couple of the fields from the People record in the new form (ID# First, Last) and the rest of the fields blank for entering the vehicle data. These records should append to the Vehicle table with the 3 fields from the People attached.
What I need as much as anything is an idea of how I should approach this. I have looked at append queries and update queries and I've been digging thru this forum for hours trying to figure it out.
Most importantly, I have ZERO understanding of VBA and only a bare minimum of SQL. I am self taught in Access, mostly using wizards for constructing anything more complex than a select query. Any help would be appreciated and please excuse my ignorance. Thanks.
Last edited by Rayce; 02-19-12 at 17:15.
Reason: error in text
After unzipping the DB, I opened it and found that the "Add Vehicle" button on the contacts form didn't perform any actions. I looked at the properties of the button and did not see any event or action "on Click".
Perhaps it is something in the zip/unzip process or a setting in the software, but there doesn't appear to be any relevant code behind the button. Please tell me if I am missing something.
Also, I found a thread in the forum (referenced in an earlier post on this thread) that seems to be what I am looking for but I am not sure where the relevant code goes.
What I think I need to do is look at the properties for the command button and select "Code Builder" when clicking on the button for "Event Procedure".
This appears to show me the code I am looking for.
Just not sure if I am in the right spot. Any thoughts?
I'm using Access 2007 and saved it in 2003... maybe that's why button didn't work. When I added the button to the form I used the wizard and told it to open another form in this case the vehicle form. So it added a macro to open the form and then I told it to open in add new record mode.
Not sure on Access 2003, but try to go to properties of the button and then click the ellipsis button on the on click event and choose macro builder. I then chose OpenForm for the action. I edited the details below to open form Vehicle and Data Mode to Add.
On the vehicle form in the on close event, I did use VBA. I chose Event Procedure and then clicked the Ellipsis button and pasted the below code in the VBA editor. It basically says when you close the vehicle form to refresh the Vehicle ID combo box to show the new vehicle in the list.
Private Sub Form_Close()
OK I think I am beginning to see daylight in the code, but I think I am going about this wrong. When the second form opens, it bring ALL the data relevant to the (ID#) matching field. What I was hoping to do was bring just the matching field and have the other fields empty for data entry. Again, it's a one to many relationship and one contact can have many vehicles. I was using this form for adding additional vehicles to a contact.
Do I need a temporary table (that the data entry goes into) that gets appended on close? I am sure I am missing some very basic DB concept here.
Tonye72, I just saw your response and will try to see if I can make sense of the code after posting this.
Unfortunately this is not the direction they want...
Tonye72 I cannot thank you enough for the help you are giving, but I am clearly not being clear enough in my request for help.
In the most recent DB you provided the peoplevehicle form was a subform in datasheets view inside a traditional form. Each of my tables has at least 20 fields in it, which is just too much data for that view. Further, the users will not be able to enter data in the datasheet format. They need a form with little holes for all the data.
So, to re-ask from my last post, do I need a holding table for the data? Essentially I want the user to enter/edit the relevant data for the contact, then click on a button and open a form for the vehicle, retaining the ID# (one to many ID for tables) and allow them to enter the data that goes into the vehicle table. If I pull the table or a query in via the second form, it populates the second form with the matching vehicle data already entered for that contact from the vehicle table. I need to pull just the ID# (first & last would be nice too) into the new form and then enter new vehicle data for that contact.
If I bring in a different table to 'hold' the data, I will need to append those records to the existing table after the fact. Clearly I am designing this wrong. I think I need to get that clear before building anything. Please forward any thoughts.
the version you sent works as advertised, but I am seeing the same problem on your side, the vehicle form comes pre-populated with ALL data from the vehicle table.
Again, I need them to be able to easily add a new vehicle to an existing contact. This will allow them to edit existing data, but not to enter a new vehicle for the same contact.
that's why I am asking about a temp table. something that doesn't already have data in it for the form to return.
Using your model, when you open the "add Vehicle" form from the contact form, the contact ID should be populated, but the vehicle fields should be blank. that is what I am trying to end up with. then the user enters the data, clicks on save and both table are updated.
What am I missing in the way I am thinking about this?
When you click on the "add new record" the vehicle ID field populates with the Autonumber field. this means that the new record will be assigned a new auto-number. Which means the vehicle won't be associated with the existing contact, but with a new number. SO Joe Smiths vehicle won't be associated with Joe Smith. Joe Smith has a single ID#, all of his vehicles have that same ID# so that when I query on his vehicles, I get them. ID# is the primary key on contacts, and the foreign key (i think) on vehicles.
and yes, in this case, I don't want them to see existing vehicles.
I am attaching screenshots. In People.jpg , note the ID#. in vehicles.jpg, the ID# is the same. Everything except the first four fields SHOULD be empty but is not.
Lastly, man, I cannot say enough how much I appreciate the help, especially considering that I must not be making myself clear.
The forms you've got look very nice. I've made another attempt to do what you're asking. When you click the Open Vehicle button it will pass the personID(contact ID in your case) to the Vehicle form (using the On click event procedure). Now when the Vehicle form opens the On Enter event procedure gets the personID and sets it to the field personID so that you can begin to enter a new vehicle record for the person (contact in your form). The vehicle form can only be opened from the person form when the button is clicked otherwise it will give an error.