Command Button Not working Properly, HELP PLEASE

[jwrnana]

Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
Dim stLinkCriteria As String
Following is info behind my Form that is opened with a command button.

stDocName = "ShipTo Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub


Several problems exist. The information is not being saved unique to the
customer and the field Customer ID on the Ship to form is reading "Name?".

 

[Ken Snell [MVP]]

Is CustomerID a numeric or text field? If it's text, delimit the value with
' characters:

stLinkCriteria = "[CustomerID]='" & Me![CustomerID] & "'"

 

[jwrnana]

I have done some testing and still having a problem. The Customer ID is a
numeric field. I went back to my query and found that it is not working.
What I am finding is that IF I have my Customer table and my ShipTo table in
the same query, I get blank fields. CustomerID is the key for customer
table and ShipToId is the primary key for the Ship to table. They are
linked by CustomerID.

What do I need to look for??
Thanks
JR

Is CustomerID a numeric or text field? If it's text, delimit the value with
' characters:

stLinkCriteria = "[CustomerID]='" & Me![CustomerID] & "'"

--

Ken Snell
<MS ACCESS MVP>

Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
Dim stLinkCriteria As String
Following is info behind my Form that is opened with a command button.

stDocName = "ShipTo Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub


Several problems exist. The information is not being saved unique to the
customer and the field Customer ID on the Ship to form is reading "Name?".

 

[Ken Snell [MVP]]

Post the SQL statement of the query that you're trying to use...

--

Ken Snell
<MS ACCESS MVP>

I have done some testing and still having a problem. The Customer ID is a
numeric field. I went back to my query and found that it is not working.
What I am finding is that IF I have my Customer table and my ShipTo table
in
the same query, I get blank fields. CustomerID is the key for customer
table and ShipToId is the primary key for the Ship to table. They are
linked by CustomerID.

What do I need to look for??
Thanks
JR

Is CustomerID a numeric or text field? If it's text, delimit the value with
' characters:

stLinkCriteria = "[CustomerID]='" & Me![CustomerID] & "'"

--

Ken Snell
<MS ACCESS MVP>

Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
Dim stLinkCriteria As String
Following is info behind my Form that is opened with a command button.

stDocName = "ShipTo Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub


Several problems exist. The information is not being saved unique to the
customer and the field Customer ID on the Ship to form is reading "Name?".

 

[jwrnana]

Following is the SQL for query with only the ShipTo Table being used. I get
all fields except the Customer ID filled in.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax, ShipTo.Email,
ShipTo.CustomerID
FROM ShipTo;

Here is the SQL for the query with ShipTo Table and Customer Table. When I
use this query, I ony get a row with no information.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax, ShipTo.Email,
ShipTo.CustomerID
FROM Customers INNER JOIN ShipTo ON Customers.CustomerID =
ShipTo.CustomerID;


What I am attempting to do is access the command button "Ship to
Information" to open the Ship to form and be able to enter the shipto
information for that customer and have only that customer's specific info
show up when looking at the ship to command button (after entering info)
rather than all customer shipto info.

Thanks in advance.

Post the SQL statement of the query that you're trying to use...

--

Ken Snell
<MS ACCESS MVP>

I have done some testing and still having a problem. The Customer ID is a
numeric field. I went back to my query and found that it is not working.
What I am finding is that IF I have my Customer table and my ShipTo table
in
the same query, I get blank fields. CustomerID is the key for customer
table and ShipToId is the primary key for the Ship to table. They are
linked by CustomerID.

What do I need to look for??
Thanks
JR

Is CustomerID a numeric or text field? If it's text, delimit the value with
' characters:

stLinkCriteria = "[CustomerID]='" & Me![CustomerID] & "'"

--

Ken Snell
<MS ACCESS MVP>

Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
Dim stLinkCriteria As String
Following is info behind my Form that is opened with a command button.

stDocName = "ShipTo Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub


Several problems exist. The information is not being saved unique to the
customer and the field Customer ID on the Ship to form is reading "Name?".

 

[Ken Snell [MVP]]

The second query will return records only if there is at least one record in
the ShipTo table that has a value for CustomerID that matches one in the
Customers table. If the CustomerID field is empty, as your first paragraph
suggests ("I get all fields except the Customer ID filled in"), then the
second query will not return any records.

--

Ken Snell
<MS ACCESS MVP>

Following is the SQL for query with only the ShipTo Table being used. I
get
all fields except the Customer ID filled in.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax,
ShipTo.Email,
ShipTo.CustomerID
FROM ShipTo;

Here is the SQL for the query with ShipTo Table and Customer Table. When
I
use this query, I ony get a row with no information.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax,
ShipTo.Email,
ShipTo.CustomerID
FROM Customers INNER JOIN ShipTo ON Customers.CustomerID =
ShipTo.CustomerID;


What I am attempting to do is access the command button "Ship to
Information" to open the Ship to form and be able to enter the shipto
information for that customer and have only that customer's specific info
show up when looking at the ship to command button (after entering info)
rather than all customer shipto info.

Thanks in advance.

Post the SQL statement of the query that you're trying to use...

--

Ken Snell
<MS ACCESS MVP>

I have done some testing and still having a problem. The Customer ID is a
numeric field. I went back to my query and found that it is not working.
What I am finding is that IF I have my Customer table and my ShipTo table
in
the same query, I get blank fields. CustomerID is the key for customer
table and ShipToId is the primary key for the Ship to table. They are
linked by CustomerID.

What do I need to look for??
Thanks
JR
Is CustomerID a numeric or text field? If it's text, delimit the value
with
' characters:

stLinkCriteria = "[CustomerID]='" & Me![CustomerID] & "'"

--

Ken Snell
<MS ACCESS MVP>

Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
Dim stLinkCriteria As String
Following is info behind my Form that is opened with a command button.

stDocName = "ShipTo Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub


Several problems exist. The information is not being saved unique
to
the
customer and the field Customer ID on the Ship to form is reading
"Name?".

 

[jwrnana]

Ken - If I go to the form itself - without going to orders, there is no
Customer ID. I can input the customer ID on that form and with BOTH tables
Customer and ShipTO, I get all information. However, when I go to the
orders form and then select the command button, Ship to Information, the
customer ID DOES NOT pull forward to the form -- which is why I have no
information in my query and thus my form.

I have created 2 forms to test my information. One form is created from
the Ship To table. In this form, I have to manually enter the customer ID.

The next form is created from the Ship to Query. When I get to the customer
ID field, the ID does not automatically appear as it should. I get an error
that says " You cannot add or change a field, because a field is required in
table "Customers". CustomerID is the primary key of customer table and is
linked to the shipto table via customer id and the command button is linking
the form Ship to to the customer and Ship to tables via customer ID.

I am lost!!

The second query will return records only if there is at least one record in
the ShipTo table that has a value for CustomerID that matches one in the
Customers table. If the CustomerID field is empty, as your first paragraph
suggests ("I get all fields except the Customer ID filled in"), then the
second query will not return any records.

--

Ken Snell
<MS ACCESS MVP>

Following is the SQL for query with only the ShipTo Table being used. I
get
all fields except the Customer ID filled in.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax,
ShipTo.Email,
ShipTo.CustomerID
FROM ShipTo;

Here is the SQL for the query with ShipTo Table and Customer Table. When
I
use this query, I ony get a row with no information.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax,
ShipTo.Email,
ShipTo.CustomerID
FROM Customers INNER JOIN ShipTo ON Customers.CustomerID =
ShipTo.CustomerID;


What I am attempting to do is access the command button "Ship to
Information" to open the Ship to form and be able to enter the shipto
information for that customer and have only that customer's specific info
show up when looking at the ship to command button (after entering info)
rather than all customer shipto info.

Thanks in advance.

Post the SQL statement of the query that you're trying to use...

--

Ken Snell
<MS ACCESS MVP>

I have done some testing and still having a problem. The Customer ID

is
a

numeric field. I went back to my query and found that it is not working.
What I am finding is that IF I have my Customer table and my ShipTo table
in
the same query, I get blank fields. CustomerID is the key for customer
table and ShipToId is the primary key for the Ship to table. They are
linked by CustomerID.

What do I need to look for??
Thanks
JR
Is CustomerID a numeric or text field? If it's text, delimit the value
with
' characters:

stLinkCriteria = "[CustomerID]='" & Me![CustomerID] & "'"

--

Ken Snell
<MS ACCESS MVP>

Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
Dim stLinkCriteria As String
Following is info behind my Form that is opened with a command button.

stDocName = "ShipTo Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub


Several problems exist. The information is not being saved unique
to
the
customer and the field Customer ID on the Ship to form is reading
"Name?".

 

[Ken Snell [MVP]]

Ahhhhh... now I see what you're doing and why it's not working.

Any reason why you cannot use ShipTo form as a subform on the order form? If
you did that, your command button could be used to make the subform visible
so that entry could be done in it -- and you could link the subform to the
main form via the CustomerID fields so that the value would carry forward.

If you want to remain with the popup form, then you'll need to use the
OpenArgs argument of the OpenForms method to carry the CustomerID value to
the form, and then have the popup form read that value and write it into the
CustomerID field on itself.

If you want this method, change your button code to this:

' start of main form's button's click code
Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
stDocName = "ShipTo Form"
DoCmd.OpenForm stDocName, , , , , , Me![CustomerID]

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub
' end of main form's button's click code


Then, in the ShipTo form, use this code (change names as needed) in the
form's Load event:

Private Sub Form_Load()
Me.CustomerID.Value = Me.OpenArgs
End If


--

Ken Snell
<MS ACCESS MVP>

Ken - If I go to the form itself - without going to orders, there is no
Customer ID. I can input the customer ID on that form and with BOTH
tables
Customer and ShipTO, I get all information. However, when I go to the
orders form and then select the command button, Ship to Information, the
customer ID DOES NOT pull forward to the form -- which is why I have no
information in my query and thus my form.

I have created 2 forms to test my information. One form is created from
the Ship To table. In this form, I have to manually enter the customer
ID.

The next form is created from the Ship to Query. When I get to the
customer
ID field, the ID does not automatically appear as it should. I get an
error
that says " You cannot add or change a field, because a field is required
in
table "Customers". CustomerID is the primary key of customer table and is
linked to the shipto table via customer id and the command button is
linking
the form Ship to to the customer and Ship to tables via customer ID.

I am lost!!

The second query will return records only if there is at least one record in
the ShipTo table that has a value for CustomerID that matches one in the
Customers table. If the CustomerID field is empty, as your first
paragraph
suggests ("I get all fields except the Customer ID filled in"), then the
second query will not return any records.

--

Ken Snell
<MS ACCESS MVP>

Following is the SQL for query with only the ShipTo Table being used.
I
get
all fields except the Customer ID filled in.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax,
ShipTo.Email,
ShipTo.CustomerID
FROM ShipTo;

Here is the SQL for the query with ShipTo Table and Customer Table. When
I
use this query, I ony get a row with no information.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax,
ShipTo.Email,
ShipTo.CustomerID
FROM Customers INNER JOIN ShipTo ON Customers.CustomerID =
ShipTo.CustomerID;


What I am attempting to do is access the command button "Ship to
Information" to open the Ship to form and be able to enter the shipto
information for that customer and have only that customer's specific info
show up when looking at the ship to command button (after entering
info)
rather than all customer shipto info.

Thanks in advance.

Post the SQL statement of the query that you're trying to use...

--

Ken Snell
<MS ACCESS MVP>

I have done some testing and still having a problem. The Customer ID is
a
numeric field. I went back to my query and found that it is not
working.
What I am finding is that IF I have my Customer table and my ShipTo
table
in
the same query, I get blank fields. CustomerID is the key for customer
table and ShipToId is the primary key for the Ship to table. They are
linked by CustomerID.

What do I need to look for??
Thanks
JR
message
Is CustomerID a numeric or text field? If it's text, delimit the value
with
' characters:

stLinkCriteria = "[CustomerID]='" & Me![CustomerID] & "'"

--

Ken Snell
<MS ACCESS MVP>

Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
Dim stLinkCriteria As String
Following is info behind my Form that is opened with a command
button.

stDocName = "ShipTo Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub


Several problems exist. The information is not being saved
unique
to
the
customer and the field Customer ID on the Ship to form is reading
"Name?".

 

[jwrnana]

Thank you so much. I should have known that!

Ahhhhh... now I see what you're doing and why it's not working.

Any reason why you cannot use ShipTo form as a subform on the order form? If
you did that, your command button could be used to make the subform visible
so that entry could be done in it -- and you could link the subform to the
main form via the CustomerID fields so that the value would carry forward.

If you want to remain with the popup form, then you'll need to use the
OpenArgs argument of the OpenForms method to carry the CustomerID value to
the form, and then have the popup form read that value and write it into the
CustomerID field on itself.

If you want this method, change your button code to this:

' start of main form's button's click code
Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
stDocName = "ShipTo Form"
DoCmd.OpenForm stDocName, , , , , , Me![CustomerID]

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub
' end of main form's button's click code


Then, in the ShipTo form, use this code (change names as needed) in the
form's Load event:

Private Sub Form_Load()
Me.CustomerID.Value = Me.OpenArgs
End If


--

Ken Snell
<MS ACCESS MVP>

Ken - If I go to the form itself - without going to orders, there is no
Customer ID. I can input the customer ID on that form and with BOTH
tables
Customer and ShipTO, I get all information. However, when I go to the
orders form and then select the command button, Ship to Information, the
customer ID DOES NOT pull forward to the form -- which is why I have no
information in my query and thus my form.

I have created 2 forms to test my information. One form is created from
the Ship To table. In this form, I have to manually enter the customer
ID.

The next form is created from the Ship to Query. When I get to the
customer
ID field, the ID does not automatically appear as it should. I get an
error
that says " You cannot add or change a field, because a field is required
in
table "Customers". CustomerID is the primary key of customer table and is
linked to the shipto table via customer id and the command button is
linking
the form Ship to to the customer and Ship to tables via customer ID.

I am lost!!

The second query will return records only if there is at least one

record
in

the ShipTo table that has a value for CustomerID that matches one in the
Customers table. If the CustomerID field is empty, as your first
paragraph
suggests ("I get all fields except the Customer ID filled in"), then the
second query will not return any records.

--

Ken Snell
<MS ACCESS MVP>

Following is the SQL for query with only the ShipTo Table being used.
I
get
all fields except the Customer ID filled in.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax,
ShipTo.Email,
ShipTo.CustomerID
FROM ShipTo;

Here is the SQL for the query with ShipTo Table and Customer Table. When
I
use this query, I ony get a row with no information.

SELECT ShipTo.ShipToID, ShipTo.ShipName, ShipTo.Address1, ShipTo.Address2,
ShipTo.City, ShipTo.State, ShipTo.Country, ShipTo.ZipCode,
ShipTo.ContactPerson, ShipTo.Title, ShipTo.Phone, ShipTo.Fax,
ShipTo.Email,
ShipTo.CustomerID
FROM Customers INNER JOIN ShipTo ON Customers.CustomerID =
ShipTo.CustomerID;


What I am attempting to do is access the command button "Ship to
Information" to open the Ship to form and be able to enter the shipto
information for that customer and have only that customer's specific info
show up when looking at the ship to command button (after entering
info)
rather than all customer shipto info.

Thanks in advance.

Post the SQL statement of the query that you're trying to use...

--

Ken Snell
<MS ACCESS MVP>

I have done some testing and still having a problem. The Customer

ID
is

a
numeric field. I went back to my query and found that it is not
working.
What I am finding is that IF I have my Customer table and my ShipTo
table
in
the same query, I get blank fields. CustomerID is the key for customer
table and ShipToId is the primary key for the Ship to table. They are
linked by CustomerID.

What do I need to look for??
Thanks
JR
message
Is CustomerID a numeric or text field? If it's text, delimit the value
with
' characters:

stLinkCriteria = "[CustomerID]='" & Me![CustomerID] & "'"

--

Ken Snell
<MS ACCESS MVP>

Private Sub Ship_to_Click()
On Error GoTo Err_Ship_to_Click

Dim stDocName As String
Dim stLinkCriteria As String
Following is info behind my Form that is opened with a command
button.

stDocName = "ShipTo Form"

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Ship_to_Click:
Exit Sub

Err_Ship_to_Click:
MsgBox Err.Description
Resume Exit_Ship_to_Click

End Sub


Several problems exist. The information is not being saved
unique
to
the
customer and the field Customer ID on the Ship to form is reading
"Name?".

 

[Ken Snell [MVP]]

You're welcome.

 

[CCross]

After applying the solution mentioned here to my subform, it works great
except, I am unable to add multiple related records to the main record.
My command button opens a travel form and I need to be able to enter more
than just one trip.
Any help-greatly appreciated.

CCross

 

[Ken Snell [MVP]]

Change the Default View of the popup form to Continous Forms.

 

[CCross]

Did that & now I get the error "Cannot find record in main table with key
matching fields" When I add a new record the key field is "0" instead of
matching the first record.--Thanks

 

[Ken Snell [MVP]]

Do you have the foreign key field as a field in the popup form's
recordsource query? Do you give the value of that foreign key field to the
popup form and write it into that field on the form?

--

Ken Snell
<MS ACCESS MVP>