Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / September 2005

Tip: Looking for answers? Try searching our database.

Command Button Not working Properly, HELP PLEASE

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jwrnana - 16 Sep 2005 21:28 GMT
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] - 16 Sep 2005 21:49 GMT
Is CustomerID a numeric or text field? If it's text, delimit the value with
' characters:

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

Signature

       Ken Snell
<MS ACCESS MVP>

> Private Sub Ship_to_Click()
> On Error GoTo Err_Ship_to_Click
[quoted text clipped - 19 lines]
> 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 - 20 Sep 2005 14:11 GMT
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:
[quoted text clipped - 24 lines]
> > 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] - 20 Sep 2005 17:48 GMT
Post the SQL statement of the query that you're trying to use...

Signature

       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.
[quoted text clipped - 38 lines]
>> > customer and the field Customer ID on the Ship to form is reading
> "Name?".
jwrnana - 21 Sep 2005 02:33 GMT
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!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.