Inserting current from open form to another form.

[Fira_g]

I have a customer form where I need a button that should copy/insert the CustomerID value to CustomerID field on the main form.

The CustomerID on the main form is a combobox with a query that selects values from the customer table.

I tried this code and it opens the main form with CustomerID field empty. I guess it is because of the CustomerID on the main form being a combobox.

Private Sub InserttoMainOrder_Click()
If IsNull(Me.CustomerID) Then
    MsgBox "Please Select a Company First", vbOKOnly
    Me.CustomerID.SetFocus
    Exit Sub
End If

DoCmd.OpenForm "frmMainOrders", , , "CustomerID = " & Me.CustomerID
End Sub

Could you please advise how to solve this?

 

[theDBguy]

Hi. Just curious, have you considered using a form/subform setup, so you don't have to worry about "copying" data from one form to another?

 

[Fira_g]

What I meant is that, I have a customer form and let say I just entered new customer details or selected existing one and want to create new order for him. As I am on this form, instead of closing this form and going to the main form to create new order for him, I just click a button on the customer form which will open the main form with the customer name already inserted in the appropriate field.

Hope that is not confusing

 

[theDBguy]

What I meant is that, I have a customer form and let say I just entered new customer details or selected existing one and want to create new order for him. As I am on this form, instead of closing this form and going to the main form to create new order for him, I just click a button on the customer form which will open the main form with the customer name already inserted in the appropriate field.

Hope that is not confusing

Hi. No, that is not confusing. I was just trying to say there may be a simpler approach than what you are currently using or thinking of using. For example, if you're on the Customer form, how exactly do you add new customers? It sounds like you have to open another form just to add a new customer when I think you should be able to simply add the new customer on the same form you were already on.

 

[Fira_g]

I have an Order table with FK CustomerID from Cusotmers table and some other fields. I've created a form based on the Order table.
On the Order Form i just need the CustomerID (which is a combobox with select query), that is all the info I need about the customer on that form.

 

[theDBguy]

I have an Order table with FK CustomerID from Cusotmers table and some other fields. I've created a form based on the Order table.
On the Order Form i just need the CustomerID (which is a combobox with select query), that is all the info I need about the customer on that form.

Hi. Are you familiar with a form/subform setup? I was just saying if you could embed your order form as a subform to your customer form, then assigning the customer id to each order will be done for you, automatically, by Access.

 

[jdraw]

Can you show us a graphic of your tables and relationships (relationships window)?

 

[theDBguy]

I would be interested in seeing the forms...

 

[Fira_g]

Here you go :

 

[theDBguy]

Here you go :

Hi Fira. Thanks. But you didn't answer my question. Are you or are you not familiar with creating subforms?

 

[Fira_g]

I am, I have a subform on my main form and few others.

 

[theDBguy]

I am, I have a subform on my main form and few others.

Okay, so back to my original question then. Have you considered using it for your orders form, so you don't have to worry about copying the customer ID back and forth? Just wondering...

 

[Fira_g]

Okay, so back to my original question then. Have you considered using it for your orders form, so you don't have to worry about copying the customer ID back and forth? Just wondering...

I am not sure if you checked the screenshot of the Order form I attached.
Are you suggesting adding customer subform to the order form?

 

[theDBguy]

I am not sure if you checked the screenshot of the Order form I attached.
Are you suggesting adding customer subform to the order form?

No, the opposite. Add your order form as a subform to the customer form (just like in the sample in the link I posted earlier).


PS. Unless, of course, each order belongs to multiple customers. If so, then yes, I am suggesting what you said. However, my original impression is each customer will have multiple orders; hence, the suggestion I described above.

 

[Fira_g]

What if I need to add just a new customer without creating an order for him?

 

[Fira_g]

Can you show us a graphic of your tables and relationships (relationships window)?

Any suggestion regarding the relationships?

 

[Fira_g]

And the subject of this thread should read:
"Inserting currentRecord from open form to another form.

 

[theDBguy]

Any suggestion regarding the relationships?

Hi. If you don't mind, I do have a couple of questions about it.

1. What is the difference between Pick and Drop locations?
2. In tblOrders, does EmployeeID refers to the person who took the order?
3. Are drivers not considered as "employees?" If they are, how come they're not in the Employees table?

That's it for now...

 

[theDBguy]

And the subject of this thread should read:
"Inserting currentRecord from open form to another form.

Hi. That's not really the same as what I thought you were asking. I thought you were wanting to know how to transfer/copy the CustomerID value from one form to another. A CustomerID value is just one value, it is not really a "record."


In any case, if you are looking for a way to copy the current CustomerID on your form to another, you could try the following syntax:


Forms!NameOfOtherForm.NameOfControlOnThatForm = Me.CustomerID


However, for the above syntax to work, the other form must also be open to accept the value you are transferring.


If you are truly asking how to copy an entire record from one form into another, that would involve a different approach/syntax.

 

[Fira_g]

Hi. That's not really the same as what I thought you were asking. I thought you were wanting to know how to transfer/copy the CustomerID value from one form to another. A CustomerID value is just one value, it is not really a "record."

That is exactly what I want, value of one field to transfer to another, I need to get the terms right. )