VBA for calling a from from another one

[Guest]

I am new to VBA and need to call "OrdersForm" from the "CustomersForm" It
should open in to a new order with customer info from "CustomersFrom" filled
in. I tried the following code in the "on Click" property of the calling
button in the CustomersForm. It opens the ordersForm in to new order (blank
with a new orderID) but the Cusotmer info which was open on the
"customersForm" does not come up in the order:
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm "ordersForm"
RunCommand acCmdRecordsGoToNew
DoCmd.GoToControl "CustomerID" (a combobox displaying Customer name)
Docmd.FindRecord stLinkCriteria
Any idea?

 

[Douglas J. Steele]

FindRecord assumes that there's already a record for that customer: you're
trying to create a new one, so FindRecord isn't appropriate.

Instead, you need to pass the CustomerID as a OpenArgs value when you're
opening the form, then use that value once it's open.

You'd open the form as:

DoCmd.OpenForm "ordersForm", OpenArgs:=Me![CustomerID]

In the Load event of ordersForm, you'd put code like:

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me!CustomerID = Me.OpenArgs
End If

End Sub

(This assumes that the textbox on the form is named CustomerID)

 

[kingston via AccessMonster.com]

I think you've got things a little mixed up or I'm not understanding your
explanation correctly. First, FindRecord is used to find an existing record
based on specified criteria. So, for example, if you had order records in
the system, this would find an order by the customer you specified. I don't
think that this is what you want to do. Second, the options in a combobox
are not "records" but rather a display of values derived from a recordset.
So, what you want to do is find the desired value and then use that value to
populate other controls. Actually, it is a bit easier than that in that you
don't have to find the correct value. You simply set it because you know
what the correct value is:

DoCmd.OpenForm "ordersForm"
RunCommand acCmdRecordsGoToNew
Forms!OrdersForm!CustomerID = Me.CustomerID

Now, if you've set up your combobox with the other information you want, you
can use it to draw data for the other controls:

Forms!OrdersForm!CustFName = Me.CustomerID.Column(1)
Forms!OrdersForm!CustLName = Me.CustomerID.Column(2)
etc.

I am new to VBA and need to call "OrdersForm" from the "CustomersForm" It
should open in to a new order with customer info from "CustomersFrom" filled
in. I tried the following code in the "on Click" property of the calling
button in the CustomersForm. It opens the ordersForm in to new order (blank
with a new orderID) but the Cusotmer info which was open on the
"customersForm" does not come up in the order:
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm "ordersForm"
RunCommand acCmdRecordsGoToNew
DoCmd.GoToControl "CustomerID" (a combobox displaying Customer name)
Docmd.FindRecord stLinkCriteria
Any idea?

 

[Guest]

thank you Douglas Your code opens the OrdersForm and populates the customer
info but it does not go to a new reocrd, instead it over writes an existing
record form another Customer (not the one in the openArgs) Looks like it
just randomly picks an existing record (with an existing orderID, an
AutoNumber field) and changes its customerId field. The idea that Kingston
has works fine however I have to populate each control manaully, since I am
learning VBA, I like to know how to activate another form into a new record
remotley using OpenArgs.

FindRecord assumes that there's already a record for that customer: you're
trying to create a new one, so FindRecord isn't appropriate.

Instead, you need to pass the CustomerID as a OpenArgs value when you're
opening the form, then use that value once it's open.

You'd open the form as:

DoCmd.OpenForm "ordersForm", OpenArgs:=Me![CustomerID]

In the Load event of ordersForm, you'd put code like:

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me!CustomerID = Me.OpenArgs
End If

End Sub

(This assumes that the textbox on the form is named CustomerID)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am new to VBA and need to call "OrdersForm" from the "CustomersForm" It
should open in to a new order with customer info from "CustomersFrom"
filled
in. I tried the following code in the "on Click" property of the calling
button in the CustomersForm. It opens the ordersForm in to new order
(blank
with a new orderID) but the Cusotmer info which was open on the
"customersForm" does not come up in the order:
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm "ordersForm"
RunCommand acCmdRecordsGoToNew
DoCmd.GoToControl "CustomerID" (a combobox displaying Customer name)
Docmd.FindRecord stLinkCriteria
Any idea?

 

[Douglas J. Steele]

Try:

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
DoCmd.GoToRecord , , acNewRec
Me!CustomerID = Me.OpenArgs
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thank you Douglas Your code opens the OrdersForm and populates the
customer
info but it does not go to a new reocrd, instead it over writes an
existing
record form another Customer (not the one in the openArgs) Looks like it
just randomly picks an existing record (with an existing orderID, an
AutoNumber field) and changes its customerId field. The idea that
Kingston
has works fine however I have to populate each control manaully, since I
am
learning VBA, I like to know how to activate another form into a new
record
remotley using OpenArgs.

FindRecord assumes that there's already a record for that customer:
you're
trying to create a new one, so FindRecord isn't appropriate.

Instead, you need to pass the CustomerID as a OpenArgs value when you're
opening the form, then use that value once it's open.

You'd open the form as:

DoCmd.OpenForm "ordersForm", OpenArgs:=Me![CustomerID]

In the Load event of ordersForm, you'd put code like:

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me!CustomerID = Me.OpenArgs
End If

End Sub

(This assumes that the textbox on the form is named CustomerID)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am new to VBA and need to call "OrdersForm" from the "CustomersForm"
It
should open in to a new order with customer info from "CustomersFrom"
filled
in. I tried the following code in the "on Click" property of the
calling
button in the CustomersForm. It opens the ordersForm in to new order
(blank
with a new orderID) but the Cusotmer info which was open on the
"customersForm" does not come up in the order:
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm "ordersForm"
RunCommand acCmdRecordsGoToNew
DoCmd.GoToControl "CustomerID" (a combobox displaying Customer name)
Docmd.FindRecord stLinkCriteria
Any idea?

 

[Guest]

Thanks again. It worked. Now I have another question: In my orders form,
which is just like the "northwind Traders DB" the ship name and address
automatically gets populated through the "After Update" event of the
"CustomerID" field. When I used your code these ship fields remains blank.
How can I have the afterupdate event of the "customerID" to trigger?

Try:

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
DoCmd.GoToRecord , , acNewRec
Me!CustomerID = Me.OpenArgs
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thank you Douglas Your code opens the OrdersForm and populates the
customer
info but it does not go to a new reocrd, instead it over writes an
existing
record form another Customer (not the one in the openArgs) Looks like it
just randomly picks an existing record (with an existing orderID, an
AutoNumber field) and changes its customerId field. The idea that
Kingston
has works fine however I have to populate each control manaully, since I
am
learning VBA, I like to know how to activate another form into a new
record
remotley using OpenArgs.

FindRecord assumes that there's already a record for that customer:
you're
trying to create a new one, so FindRecord isn't appropriate.

Instead, you need to pass the CustomerID as a OpenArgs value when you're
opening the form, then use that value once it's open.

You'd open the form as:

DoCmd.OpenForm "ordersForm", OpenArgs:=Me![CustomerID]

In the Load event of ordersForm, you'd put code like:

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me!CustomerID = Me.OpenArgs
End If

End Sub

(This assumes that the textbox on the form is named CustomerID)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am new to VBA and need to call "OrdersForm" from the "CustomersForm"
It
should open in to a new order with customer info from "CustomersFrom"
filled
in. I tried the following code in the "on Click" property of the
calling
button in the CustomersForm. It opens the ordersForm in to new order
(blank
with a new orderID) but the Cusotmer info which was open on the
"customersForm" does not come up in the order:
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm "ordersForm"
RunCommand acCmdRecordsGoToNew
DoCmd.GoToControl "CustomerID" (a combobox displaying Customer name)
Docmd.FindRecord stLinkCriteria
Any idea?

 

[Douglas J. Steele]

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
DoCmd.GoToRecord , , acNewRec
Me!CustomerID = Me.OpenArgs
Call CustomerID_AfterUpdate
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks again. It worked. Now I have another question: In my orders form,
which is just like the "northwind Traders DB" the ship name and address
automatically gets populated through the "After Update" event of the
"CustomerID" field. When I used your code these ship fields remains blank.
How can I have the afterupdate event of the "customerID" to trigger?

Try:

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
DoCmd.GoToRecord , , acNewRec
Me!CustomerID = Me.OpenArgs
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thank you Douglas Your code opens the OrdersForm and populates the
customer
info but it does not go to a new reocrd, instead it over writes an
existing
record form another Customer (not the one in the openArgs) Looks like
it
just randomly picks an existing record (with an existing orderID, an
AutoNumber field) and changes its customerId field. The idea that
Kingston
has works fine however I have to populate each control manaully, since
I
am
learning VBA, I like to know how to activate another form into a new
record
remotley using OpenArgs.

:

FindRecord assumes that there's already a record for that customer:
you're
trying to create a new one, so FindRecord isn't appropriate.

Instead, you need to pass the CustomerID as a OpenArgs value when
you're
opening the form, then use that value once it's open.

You'd open the form as:

DoCmd.OpenForm "ordersForm", OpenArgs:=Me![CustomerID]

In the Load event of ordersForm, you'd put code like:

Private Sub Form_Load()

If IsNull(Me.OpenArgs) = False Then
Me!CustomerID = Me.OpenArgs
End If

End Sub

(This assumes that the textbox on the form is named CustomerID)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am new to VBA and need to call "OrdersForm" from the
"CustomersForm"
It
should open in to a new order with customer info from
"CustomersFrom"
filled
in. I tried the following code in the "on Click" property of the
calling
button in the CustomersForm. It opens the ordersForm in to new order
(blank
with a new orderID) but the Cusotmer info which was open on the
"customersForm" does not come up in the order:
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm "ordersForm"
RunCommand acCmdRecordsGoToNew
DoCmd.GoToControl "CustomerID" (a combobox displaying Customer
name)
Docmd.FindRecord stLinkCriteria
Any idea?