Using combo box to populate a sub form which is used to create a new record

70 pts.
Using Access 2003 I have a table called POTable which contains 4 fields. PONOID (primary Key and AutoNumber), PONO (text), VENDOR (text), GENDESCRIP (text). I have another table called MRRTable. This table contains several fields with one of them being PONOID (number). What I want to do is the following: I want to create a new record in my MRRTable each time a shipment arrives (MRR = Material Receipt Record). I need create a user friendly form to have an admin perform this entry rather than myself or someone going into the back end and simply adding to a table or query. This form I want to create for adding a new MRR to the table must contain the PO number. I don’t want the person to start entering a new record only to find out there is no PO Number for this item and they have to stop in mid stream. So, what I was thinking was to set up a main unbound form which has a combo box listing all the PO numbers/Vendors in the table. The admin would look at the combo box and select the PO number for that specific shipment (If the PO isn’t there, they would select a separate button to add the PO information this adding it to the combo box). After the PO number has been selected from the combo box, I then want them to click the "Add MRR" button and go to the Add MRR input form (thinking this would be a subform already in the main form). When the focus is moved to the Add MRR subform, I want the PO Number (PONOID) to auto populate in the field called PONOID which is a field in the MRR Table. Then they can fill out the rest of the fields per the directions. When complete, they will click “Add Package No” button and it will take them to the additional subform called Add Package No. ready to enter a new record for the package table but bringing down the newly created MRR # and auto populating it into the MRRNOID field in the “add package no” input form. It's a progression. All I need to do is figure out how I can get the value from the original combo box to populate a designated field in my subform called Add MRR which is going to used to create a new record in that MRR Table. Suggestions? Is this a good approach? Any help will be most appreciated. Thanks, George Bullo

Software/Hardware used:
MS Access 2003, Windows XP

Answer Wiki

Thanks. We'll let you know when a new response is added.

In the MRR subform’s BeforeUpdate event, you could fill in the PONOID if it is blank.

if isnull(me![PONOID]) then ‘new record or PONOID is missing, so fill in the PONOID
me![PONOID] = forms![mainform]!
end if

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Randym
    I should add that you could also use the parent/child relationship between the unbound main form and the bound subform. Then the PONOID would populate automatically.
    1,740 pointsBadges:
  • Gbullo
    Randy, I'm not completely understanding this. I created a main form but it looks as if I have to make it a bound form in order to make a parent/child relationship to a sub form. Even with that information and the syntax you recommended, I can't seem to get the results needed. I see where I may have also not clearly identified what my exact needs are. This is what I’m trying to accomplish. From a Maintenance form, the user will click a button called New MRR That will open a form (I envision a form with a few subforms on the same working pane) The top part (main form) will have a Combo Box and two buttons. (The combo box will show a list of PO#’s, vendor names, etc. One button is for adding a new PO and the other button is for adding a new MRR once the PO has been selected from the Combo Box) They pull down the drop down box and find the desired PO# and select it. Click the button Add MRR (This will open up or go to the Object (subform) in Data Entry Mode based on a Query from the underlining table (MRRTable) Since this is a Data Entry form, I won’t know what the next auto assigned number is but I need this number to be visible somehow) Once the Add MRR form or Subform is opened or has focus, I want the value from the Combo Box to auto populate the field named PONOID and then move focus to a control called MRR Date. (I’m hoping that since this form is Data Entry, by the action of placing a value in PONOID and then losing focus, it will then show the auto assigned number. I believe that this auto assigned number isn’t part of the new record until an entry has been made in a field and it loses focus. I need this new Auto Assigned Number). I’ve done some of it already but I need help to get the rest to work. (Those items with an * is what I already designed) I'm starting from a balnk form. These are the steps I've taken and where I am not getting results. *Created an unbound form in design vew *Dropped a combo box on the form and the wizard started *I selected "I want the Combo Box to look up the value" *I selected the table where the data resides (POTable) *Brought over all the fields *Sorted by field PONO *Named the Combo Box "CmbPONO" *Viewed the new form in form view and I can make a selection from the list. This works all works. *Created a form called SubformAddMRR bound to query called QryAddMRR *Changed the properties to Add Records (The form works and is ready for data entry) This is where I am having problems. I can’t get the original combo box to populate the field in the SubformAddMRR with the value that was selected. I want the results from the selection made in the drop down box to automatically populate a field called PONOID in the SubfrmAddMRR (which I want to be a sub form of the initial form they opened). I hope I have given enough details. Thanks for all your help so far. Regards, George Bullo
    70 pointsBadges:
  • Dmenke38
    Gbullo, The reason your selected data is not being transferred is that the unbound form does not automatically link to the P.O. table. In Access, the act of dropping a form on another form sets off some hidden actions that depend on an established relationship between the data table on the parent form and the data table used by the sub-form. Your scenario lacks one imprtant feature. The parent form is not bound to a data table, thus Access can not complete the link-up for you. You could achieve the desired result in one form bound to the (purchase order) table, allowing the add action, and using the combo boxes to populate the attribute fields in the new record. The Automatically assigned primary key, which I take is you P.O number, will show in the PONOID field on the bound form when the record is posted. However you could use the forms you have by transferring the parent form data into the appropriate subform fields using the subform On Activate event procedure. The appropriate syntax for the transfer is: Me![Field1Name] = Forms![Parent formName]![Field1Name] Me![Field2Name] = Forms![Parent formName]![Field2Name] (etc....) I struggled with this same problem in my early experience with Access, not realizing that using an unbound form with combo boxes merely made the selections available, but did not do anything else to use it on a linked form.
    185 pointsBadges:
  • Gbullo
    I'm going to have to plead stupid. I have no idea what the Me! means in front of the syntax. I've seen this before when people talk about expressions but I've never known what that means. I'm failing miserably in trying to get this simple task to work. It seems like it should be so simple to look up a value and have that value populated in another field. I just can't seem to figure it out even with all the help I have been getting. I'm begining to wonder if I have the wrong approach to get some data to auto populate just so a person doesn't have to look records elseware.
    70 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: