Help with Form

[goduke]

First, I am using Access 2003. I have two tables: Customer and Order, linked on Customer_ID.

I have 2 forms: Customer Form (Parent) that is linked to the Order Form (Child). I used the wizard to link the two forms since I dont know the VBA code too well. The wizard gives me the button which jumps from the Parent form to the Child form and everything works fine. However, on both forms I have the Customer_ID, which is autopopulated on the Parent form, but when I jump over to the Child form, it does not populate the Customer_ID in that field. I have both fields in each table, and it is the foreign key in the Orders table, so how can I get it to populate once I jump over. As of now, on the Child form, I am using a combo box to look up all the customers in the customer table, then when you select the customer, it populates their Customer_ID in the field. How can I get it to populate when I go from the Parent form to the Child form??? Any help would be great! Thanks.

CM

 

[dhookom]

This "synchronization" is generally automatic if you have the Link Master/Child properties set to the common field (Customer_ID). There should be no reason to create any code or combo boxes to do this. The default value of the Customer_ID field in the subform will be the Customer_ID value from the main form.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

The wizard gives me the button which jumps from the Parent form to the Child form and everything works fine
[/code]

To me this sounds like you do not have a subform, but 2 separate forms. The term child form is usually used to mean a subform on a mainform. You can do this popping open another form but a subform is a much easier way of synching and working with Parent child relations. If this is the case, any reason for not using a subform?

 

[goduke]

MajP, you are correct. I am not using subforms, but two separate forms. Like I mentioned above, I used the wizard to link these two forms together, so they are linked. Let me see if I can explain something else. If I have a customer, who already has an order, and I'm on the customer form and click the button, it takes me to the orders for that customer. However, if the customer is either new, or doesnt have any orders, when I click the buttom and it takes me to the order form, the customer_id does not populate. Do I have to fill out the information on the order form and save it in order for the customer_id to populate?

 

[MajP]

Your terminology is a little off and it caused some confusion. You do not really have a link, the code allows you to open a form filtered to that person. Linking is usually used to mean synchronized, so if you change a record in one location it changes the group of records in another location. This is what a subform does.

Again a subform will do all of this for you, and it will be a lot easier. It allows you to add new records for a user, and see all records for that customer.

However if you used the wizard you probably got something like

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmOrders"
    
    stLinkCriteria = "[intVals]=" & Me![id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Forms(stDocName).intVals.DefaultValue = Me!id

Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click
    
End Sub

I added this
Forms(stDocName).intVals.DefaultValue = Me!id

It sets the default value for the new records based on the record that called it.

 

[goduke]

Here is my code for the button.

Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![Order Information].DataEntry = True
Else
Forms![Order Information].Filter = "[Customer_ID] = " & Me.[Customer_ID]
Forms![Order Information].FilterOn = True
End If

End Sub

 

[MajP]

since you are in data entry then filter really does not do anything. Filter returns existing records with a matching customer ID. Therefore you need to set the default property, like I showed above so any new record gets a order information default value matching the customerID.

 

[goduke]

Right, I do want it to return existing records, but whenever I have a new order for a particular person, I want it to populate that field. Do I have to declare the variables like you mentioned above or what? Like I said, I dont understand the VBA code very well. Can you send me the exact syntax? You have been a great help so far, so thanks for everything!!!