Inserting current from open form to another form. (1 Viewer)

F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
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.

Code: 
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

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:56
Joined
Oct 29, 2018
Messages
21,455
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?
 
F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
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

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:56
Joined
Oct 29, 2018
Messages
21,455
Fira_g said:
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 :)
Click to expand...
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.
 
F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
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

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:56
Joined
Oct 29, 2018
Messages
21,455
Fira_g said:
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.
Click to expand...
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

jdraw

Super Moderator
Staff member
Local time
Yesterday, 23:56
Joined
Jan 23, 2006
Messages
15,379
Can you show us a graphic of your tables and relationships (relationships window)?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:56
Joined
Oct 29, 2018
Messages
21,455
I would be interested in seeing the forms...
 
F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
Here you go :
 

Attachments

  • main.png
    main.png
    26 KB · Views: 107
  • relat.png
    relat.png
    92.6 KB · Views: 93
F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
I am, I have a subform on my main form and few others.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:56
Joined
Oct 29, 2018
Messages
21,455
Fira_g said:
I am, I have a subform on my main form and few others.
Click to expand...
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...
 
F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
theDBguy said:
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...
Click to expand...

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

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:56
Joined
Oct 29, 2018
Messages
21,455
Fira_g said:
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?
Click to expand...
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.
 
F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
What if I need to add just a new customer without creating an order for him?
 
F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
And the subject of this thread should read:
"Inserting currentRecord from open form to another form.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:56
Joined
Oct 29, 2018
Messages
21,455
Fira_g said:
Any suggestion regarding the relationships? :rolleyes:
Click to expand...
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

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:56
Joined
Oct 29, 2018
Messages
21,455
Fira_g said:
And the subject of this thread should read:
"Inserting currentRecord from open form to another form.
Click to expand...
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.
 
F

Fira_g

Registered User.
Local time
Yesterday, 23:56
Joined
Oct 17, 2019
Messages
60
theDBguy said:
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."
Click to expand...

That is exactly what I want, value of one field to transfer to another, I need to get the terms right. )
 
You must log in or register to reply here.

Similar threads

N
Solved How to reference a unbound combo box column in Ms Access
Replies
11
Views
697
nector
N
S
Solved Parameter Dialog on Open Form
Replies
5
Views
523
silentwolf
S
C
Conditionally formatting labels and other controls
Replies
0
Views
200
CJ_London
C
N
OPening Record Source in Ms Access VBA
Replies
13
Views
1,206
nector
N
S
How to get value from form in Navigation form
Replies
8
Views
864
LarryE
LarryE

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom