Post the SQL statement of the query that you're trying to use...
>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.
[quoted text clipped - 38 lines]
>> > customer and the field Customer ID on the Ship to form is reading
> "Name?".
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...
>
[quoted text clipped - 40 lines]
> >> > customer and the field Customer ID on the Ship to form is reading
> > "Name?".
Ken Snell [MVP] - 21 Sep 2005 21:02 GMT
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.

Signature
Ken Snell
<MS ACCESS MVP>
> Following is the SQL for query with only the ShipTo Table being used. I
> get
[quoted text clipped - 76 lines]
>> >> > customer and the field Customer ID on the Ship to form is reading
>> > "Name?".
jwrnana - 22 Sep 2005 04:51 GMT
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
[quoted text clipped - 82 lines]
> >> >> > customer and the field Customer ID on the Ship to form is reading
> >> > "Name?".
Ken Snell [MVP] - 22 Sep 2005 23:05 GMT
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

Signature
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
[quoted text clipped - 120 lines]
>> >> >> > customer and the field Customer ID on the Ship to form is reading
>> >> > "Name?".
jwrnana - 23 Sep 2005 01:20 GMT
Thank you so much. I should have known that!
> Ahhhhh... now I see what you're doing and why it's not working.
>
[quoted text clipped - 159 lines]
> >> >> >> > customer and the field Customer ID on the Ship to form is reading
> >> >> > "Name?".
Ken Snell [MVP] - 23 Sep 2005 03:09 GMT
You're welcome.

Signature
Ken Snell
<MS ACCESS MVP>
> Thank you so much. I should have known that!
CCross - 26 Sep 2005 21:56 GMT
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
> You're welcome.
>
> > Thank you so much. I should have known that!
Ken Snell [MVP] - 26 Sep 2005 22:23 GMT
Change the Default View of the popup form to Continous Forms.

Signature
Ken Snell
<MS ACCESS MVP>
> 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.
[quoted text clipped - 7 lines]
>>
>> > Thank you so much. I should have known that!
CCross - 26 Sep 2005 22:33 GMT
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
> Change the Default View of the popup form to Continous Forms.
>
[quoted text clipped - 9 lines]
> >>
> >> > Thank you so much. I should have known that!
Ken Snell [MVP] - 27 Sep 2005 03:51 GMT
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?

Signature
Ken Snell
<MS ACCESS MVP>
> 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
[quoted text clipped - 15 lines]
>> >>
>> >> > Thank you so much. I should have known that!