Referencing field on Main form to save to Subform bound table

[Design by Sue]

I have a main form (PackFRM) with a subform. On the main form the user is to select a "pack" then on the subform (ItemSFM) the user will select the "items" to be added to the "pack".

The main form is unbound and based on a query of the pack table. Once the user selects the pack name from a combo box, related information is displayed, including the pack number (PackID). The pack number is displayed in a text box that is based on a column of the query of the pack name combo box.

Then the user tabs to the subform, which is bound to the combination table, and there selects the item (ItemID) and enters the quantity (Quantity).

The bound table for the subform has 3 fields, the pack number (Pack ID), the item number (ItemID) and the quantity (Quantity). The item number and quantity are included on the subform but the pack number is on the main form. How can i get the record of the subform to save and include the pack number from the main form?

Sue

 

[boblarson]

Set the subform control's MASTER/CHILD properties to Pack ID.

 

[Design by Sue]

Just checking - are you saying put PackID in the "Link Master Fields" of the subform?. If so do I put anything in the "Link Child Fields"? or is there another property called master/child?

Sue

 

[boblarson]

Just checking - are you saying put PackID in the "Link Master Fields" of the subform?. If so do I put anything in the "Link Child Fields"? or is there another property called master/child?

Sue

You set the MASTER link to be the field that is in the main form's record source which corresponds to the one you want in the subform and the CHILD link is the field in the subform's record source which ties them together.

 

[Design by Sue]

Thanks - Appreciate the prompt response! I will test this out shortly and confirm if I got it working!

Sue

 

[Design by Sue]

Another question on this - once I get this subform working, I need to add another for the components - the subform will be the same as subform 1 but subform 2 will be linked to a different table, but will need to refer to the PackID as does subform 1. Is this possible and how would I connect the 2nd subform?

Sue

 

[boblarson]

Another question on this - once I get this subform working, I need to add another for the components - the subform will be the same as subform 1 but subform 2 will be linked to a different table, but will need to refer to the PackID as does subform 1. Is this possible and how would I connect the 2nd subform?

Sue

Is the 2nd subform really just the components of the pack? If so, you can leave it on the main form too with the same exact master/child links as the first one.

 

[Design by Sue]

It is bound to a different table (the components). Both the items and components will be on bound forms to different tables, the main form is not bound.

 

[boblarson]

You wrote:

The main form is unbound and based on a query of the pack table.

So why not bind it so that the values can easily be updated using the subform. The main form doesn't have to use an updateable query to be able to be used that way.

It would simplify matters.

 

[Design by Sue]

I am not sure what you are saying - what should I bind the main form to? The records it is displaying are in a 3rd table (Pack TBL)

Sue

 

[boblarson]

I am not sure what you are saying - what should I bind the main form to? The records it is displaying are in a 3rd table (Pack TBL)

Sue

Yes, the 3rd table. If PackID is there and you are using the main form to select and display those records (albeit a single one based on the selection), you can bind the main form to that source (I would create a non-updateable query on that table so that nobody can change the displayed values.

The thing to remember is normally a form should usually be used for a single table. And for subforms you don't want to use the same table that is being used by the parent form. So, in this case, you have the table for Packs. It would be considered the ONE in a one-to-many relationship. Then the subform handles the MANY part to that. Many components to ONE pack. And by linking them on the subform Access will add that pack id of the selected record on the parent form to the subform's table automatically whenever a record is entered. And so as it goes with the other table.

 

[Design by Sue]

I may be wrong, but I think that is what I am trying to do

the main table is linked to a query of the Pack table so no changes can be made, it just reflects the information in the table
the 1st subform has the Item table as its control source,and when a record is saved it is to save the 3 records, pack id, item id and quantity to the Item table
the 2nd subform has the Component table as its control source and when a record is saved it is to save 3 records, pack id component id and quantity to the component table

So my question was, can I link the pack id to the 2nd subform as I am to do in the first subform per previous posts?

Sue

 

[boblarson]

I may be wrong, but I think that is what I am trying to do

the main table is linked to a query of the Pack table so no changes can be made, it just reflects the information in the table

But you told me the form was UNBOUND. Which is it? If it has the query in the form's RECORD SOURCE property it is bound. If there is nothing in the form's record source property it is unbound. It should be bound in this case.

the 1st subform has the Item table as its control source,and when a record is saved it is to save the 3 records, pack id, item id and quantity to the Item table

If linked to the main form like I said, it will do just that.

the 2nd subform has the Component table as its control source and when a record is saved it is to save 3 records, pack id component id and quantity to the component table

And, again same setup as the first subform because PACKID is the key here.

 

[Design by Sue]

OK - I am sorry now I understand what you are saying - the mainform has as it's row source the query. I thought it was unbound because there was nothing in the control source. Is this the correct way to do it?

AND not I have another issued on the subform. The user selects the Description of the item. On the after update event of the description combo, I have Me.Item_ID = Me.Description.Column(2) so that the id value gets entered into the text box. When it updates, the item id does show up for a second then flashed to null. Also when the user enters a quantity the same thing happens. Obviously I have something added where I shouldn't but I have checked the code and can't see anything. Do you have a suggestion of what might be causing this?

Both controls that are going blank are the bound fields to the item table.

Sue

 

[Design by Sue]

What it is doing is saving a record in the table immediately when I select the description and when I enter the quantity. What would cause it to save like this?

Sue

 

[boblarson]

What it is doing is saving a record in the table immediately when I select the description and when I enter the quantity. What would cause it to save like this?

Sue

you mean in the first subform? When you start a record in the subform it will start putting in the table. It only saves when you move to another record or out of the subform UNLESS you have specific code to save in the event of any of the controls on the subform.

 

[Design by Sue]

Sorry for the machine gun posts, but when I have the subform set to dataentry, which I think is correct for this use, I get the results stated, but if I change the dataentry property to no I get the error message "This expression is typed incorrectly, or it is too complex to be evaluated."

This is so weird!

Sue

 

[boblarson]

First off. DataEntry means that no existing records will be shown. And normally in a subform that is NOT what you want.

Can you post a copy of your database with fake data so I can take a look at what you've goe?

 

[Design by Sue]

Yes - the subform. Yes when the afterupdate event fires to update the item id it saves or if you type in a quantity it saves

 

[Design by Sue]

here is the code in the subform
I can't see anything here that would cause it






Private Sub Description_AfterUpdate()
Me.Item_ID = Me.Description.Column(2)
End Sub

Private Sub Description_GotFocus()

If IsNull(Me.Parent![P Description]) Then
MsgBox "You must enter choose a prepack before you can add items.", vbExclamation, "Information Needed"
Me.Parent![P Description].SetFocus
Exit Sub
End If

End Sub



'------------------------------------------------------------
' Save_BTN_Click
'
'------------------------------------------------------------
Private Sub Save_BTN_Click()
On Error GoTo Save_BTN_Click_Err

If IsNull(Me.Parent![P Description]) Then
MsgBox "You must enter choose a prepack before you can add items.", vbExclamation, "Information Needed"
Me.Parent![P Description].SetFocus
Exit Sub
End If


If IsNull(Me.Description) Then
MsgBox "You must select an item.", vbExclamation, "Information Needed"
Me.Description.SetFocus
Exit Sub
End If


If IsNull(Me.Quantity) Then
MsgBox "You must enter a quantity.", vbExclamation, "Information Needed"
Me.Quantity.SetFocus
Exit Sub
End If

DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec


Me.Description.SetFocus



On Error Resume Next

Save_BTN_Click_Exit:
Exit Sub

Save_BTN_Click_Err:
MsgBox Error$
Resume Save_BTN_Click_Exit

End Sub


'------------------------------------------------------------
' Close_BTN_Click
'
'------------------------------------------------------------
Private Sub Close_BTN_Click()
On Error GoTo Close_BTN_Click_Err

DoCmd.Close , , acSaveNo

If CurrentProject.AllForms("Combinations FRM").IsLoaded Then
DoCmd.Close , "Combinations FRM", acSaveNo
End If



Close_BTN_Click_Exit:
Exit Sub

Close_BTN_Click_Err:
MsgBox Error$
Resume Close_BTN_Click_Exit

End Sub


'------------------------------------------------------------
' Clear_BTN_Click
'
'------------------------------------------------------------
Private Sub Clear_BTN_Click()
On Error GoTo Clear_BTN_Click_Err

On Error Resume Next
DoCmd.RunCommand acCmdUndo



Clear_BTN_Click_Exit:
Exit Sub

Clear_BTN_Click_Err:
MsgBox Error$
Resume Clear_BTN_Click_Exit

End Sub