If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Access 2003 Newbie needs help

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 29
Access 2003 Newbie needs help

Howdy,

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 16:15. Reason: error in text
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 29
I think this is what I need, not sure how to implement

I believe that this thread:
Need to open form and pass a value is relative to what I want to do, but A) I am not sure if it applies, and B) I am not sure how/where to implement the code.

Again, any help is appreciated.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 23
Hi,

I'm new to Access too., but I've attached a sample database of what I think you're asking.
Attached Files
File Type: zip contactvehicle.zip (32.2 KB, 2 views)
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 29
The zipped DB doesn't function as expected

Thanks for the sample DB tonye72.

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?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 23
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()
Forms!people.vehicleID.Requery
End Sub
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 29
I'm asking the wrong question

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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 23
I had the one to many backwards to what you just said. Try this database I'm attaching. It has a Form for the person and then a subform for the vehicles.
Attached Files
File Type: zip contactvehicle1.zip (43.9 KB, 3 views)
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 29
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 23
Ok, here is a form to enter people and then a button to open the vehicle form where you can add many vehicles to the person. Hopefully I'm understanding you.
Attached Files
File Type: zip contactvehicle2.zip (22.8 KB, 2 views)
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 23
Here is the on click event procedure for the button if it doesn't work.

Private Sub VehicleForm_Click()
On Error GoTo Err_VehicleForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "vehicle"

stLinkCriteria = "[personID]=" & Me![personID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_VehicleForm_Click:
Exit Sub

Err_VehicleForm_Click:
MsgBox Err.Description
Resume Exit_VehicleForm_Click

End Sub
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 29
new version, same problem

Thanks again for all the help.

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?
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 23
When the vehicle form opens, wouldn't they click the add new record at the bottom.

Are you saying you don't want to see previous entered vehicle records on the person... just be able to add new ones?
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 29
making rogress

Tonye72,
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.
Attached Thumbnails
Access 2003 Newbie needs help-people.jpg   Access 2003 Newbie needs help-vehicles.jpg  
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 23
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.
Attached Files
File Type: zip contactvehicle3.zip (29.0 KB, 5 views)
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 29
I think that's really close

Tonye72,
I think that's very close to what I am looking for. Please tell me what you did to accomplish this.

I don't really need the Unique ID for each vehicle, but will be glad to add it if it is what makes this possible.

Again, how did you get there?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On