Randym   1410 pts.  |   Sep 23 2009  3:52PM GMT

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.

Gbullo   70 pts.  |   Sep 24 2009  2:57PM GMT

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

Dmenke38   185 pts.  |   Sep 25 2009  2:46PM GMT

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.

Gbullo   70 pts.  |   Sep 25 2009  7:45PM GMT

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.