New to Access

[JL_CAD]

I believe this should be a simple task, but I have had no luck.
I currently have a database setup with a table that contains the feilds: part number, cutomer name, mold number, and others. I want to make a form to enter new data into the table under those fields. I went through the wizard to create a form from the existing table, but I want to be able to do the following:
Use combobox for customer field to add or use existing customer.
Lookup mold numbers under the selected customer and use existing or enter new mold number.
Lookup part numbers under selected mold number and enter new part number.

 

[anvil19]

Hey JL_CAD

To create a form that is automatically bound to a table I just Right_Click the table name and SaveAs a Form. The form created is a long list of the fields in the table, but it can be searched by a user.

Click on the Design button and drag the controls around until you have them all in the right places. Save the form.

If you want to add data to a table via the form you could add a CommandButton to the form. When the button appears on the form a dialog will pop up and ask you if you wish to use the button to add a record to the table. Choose yes and all the code behind the button will be added by the system. Every time you press that button a new record is added to the table, and your Form will show empty controls ready for you to add data to the table. All the constraints and validation rules that apply to the table apply to the controls on the form.

Try that first and then we can continue with the ComboBox controls...

anvil19

 

[JL_CAD]

ok,
I have the form saved with the fields I want to keep and now I need to set up the comboboxes so that they will show the existing table entries based on the customer or mold # I select. For example:
If I select "Hunter" as the customer, then I want the mold# and part# comboboxes to show only entries with "Hunter" as the customer. And then if I select "HUN04" as the mold# with "Hunter" in my customer combobox, it should only show part#'s with "HUN04" as the mold#.
I hope this makes sense.

 

[JL_CAD]

i was able to change the textboxes to comboboxes, but I am having trouble getting the boxes to filter from previous selections.
anyone know how to get this to work?

 

[bat17]

Check out this article on The Access Web


HTH

Peter

 

[JL_CAD]

I have been able to get it close but I am having trouble getting the code to produce " around the customer value. I need "HUNTER" and I'm getting HUNTER for the expression.

Private Sub CUSTOMER_AfterUpdate()

Dim strSQL As String
strSQL = "Select [Machine Setup Sheet].[PART #]FROM [Machine Setup Sheet] WHERE [Machine Setup Sheet].[CUSTOMER]= " & Customer
PART.RowSourceType = "Table/Query"
PART.RowSource = strSQL

End Sub

 

[bat17]

Try

Private Sub CUSTOMER_AfterUpdate() 

Dim strSQL As String 
strSQL = "Select [Machine Setup Sheet].[PART #]FROM [Machine Setup Sheet] WHERE [Machine Setup Sheet].[CUSTOMER]= '" & Customer & "'"
PART.RowSourceType = "Table/Query" 
PART.RowSource = strSQL 

End Sub

HTH

Peter

 

[JL_CAD]

Thanks for the help.
I was able to get this to work:

Private Sub CUSTOMER_AfterUpdate()
Const cQUOTE = """"
Dim strSQL As String
strSQL = "Select [Machine Setup Sheet].[MOLD #]FROM [Machine Setup Sheet] WHERE [Machine Setup Sheet].[CUSTOMER]= " & cQUOTE & Customer & cQUOTE
MOLD.RowSourceType = "Table/Query"
MOLD.RowSource = strSQL

End Sub

This works for now, but I am getting duplicates in my combobox. Is there a way to display unique values only.
For example, there are multiple part #'s that have the same mold#. So the combobox displays HUN01 4 times because there are 4 parts that run in HUN01. I want the combobox to display only 1 instance of HUN01.

 

[anvil19]

Hi JL_CAD

Just add the DISTINCT statement after the SELECT statement , like so

strSQL = "Select DISTINCT [Machine Setup Sheet].[MOLD #]FROM ...

Try that!

anvil19