I was wondering if it was possible to Limit the results of a products Combobox results by manufacturer on the querry itself.

I am using three tables

[u]Vendors</u>MFGID - PK
Name

[u]Products</u>
PartID - PK
MFGID - FK
Partnumber
Description
Cost

[u]Order Details</u>
JobID - PK
PartID- FK
Jobnumber

So far I have the Job Referencing the Parts Correctly but I cant seem to limit the list down.

Would I need to place this in the form instead of someplace in the query?

Private Sub MFGID_GotFocus()
  Me.cmbMFGID.setfocus
End Sub

Private Sub MFGID_AfterUpdate()
 Me.cmbPartID.Requery
 Me.cmbPartID = Me.cmbPartID.ItemData (0)
 Me.cmbPartID_AfterUpdate
End Sub


Private Sub Form_Current ()
 If Not IsNothing(Me.MFGID) Then
 Me.cmbMFGID = Me.MFGID
End If
 Me.cmbPartID.Requery
End Sub

Private Sub PartNumber_GotFocus()
Me.cmbPartID.setfocus
End Sub


Does that sound right or is there a way to do this in the query itself rather than on the form? or is one method better than another?

I am not sure what you mean. Do you want to limit the query results to one manufacturer? If so, where are you specifying this parameter?

If it is in a combo box on a form that is launching the query, and the query is only used for this form, then put the parameter in the criteria line in the filed in the query designer:

[Forms]![frmFormName].[cboComboBoxName]

If you are using the query for other processes, then pass it with a criteria statement on the on click event of the button:

Dim iMan As Integer 'passing a PK integer
Dim sLink As String

iMan = Me.cboComboBoxName

sLink = "[MFGID] = " & iMan

DoCmd.OpenForm sFormName, , , sLink 'check syntax to make sure the string is in the WHERE section of the DoCmd line you are using.

Did that help?


mmcdonal

Simply put I am trying to make it so that when I select a MFGID only the PartIDs of that MFGID are shown.

The Querry is only used by one Form.

I am not sure which method would be best with those constraints.

Thanks!

Use the first then. Does it make sense?

mmcdonal

Your Directions are very clear, but I think I might not be describing it well. If I put the

[Forms]![frmFormName].[cboComboBoxName]

In Criteria under MFGID in the Query do i still need to put the Subs that I Wrote earlier in the cmbMFGID ?

The reason I ask is that the form is dependant on the query, wouldnt this make the Query now dependant on the form? Is that ok?

Here are the actions that I am anticipating:

You open a form, and select the MFGID on that form.

You click a button on that form, and it opens a second form based on the query results.

If that is not the case, please explain in what order the events take place, and what is based on this query.


mmcdonal

I am Horrible at this =(

Here it goes.

Okay the user clicks a button that says "Add Parts to a Job".

Event- Open new Form that has the query fields

Field - 1 JobID - User First Selects the Job he want to attach the part too

Field - 2 MFGID - User Selects the Manufacturer to limit the parts that populate in the next field

Field - 3 PartNumber - User selects the part he wants.

Field - 4 Description - AutoPopulates -(I have this working)

Field - 5 Price - Autopopulates _ (I Have this working)

That is basicaly what I am trying to accomplish. What I dont know is the best way to do it.

Oooh, cascading combo boxes. Is that all? Check the other posts for this for now. I will reply back in a couple hours if you are still having problems. I posted on this recently, so it might be in my post list.

mmcdonal

OKay so this is what i found but i think i am still doing something wrong its all errors!
In the Events for after update on my MFGID Box I Put this.

Private Sub PKMFGID_AfterUpdate()
Dim strSQL As String
    strSQL = "Select" & Me!PKMFGID
    strSQL = strSQL & "from Categories"
    Me!PartID.Query = "SubOrder"
    Me!PartID.RowSource = strSQL
    Me!PartID.Requery
End Sub

In the Row Source for my PartID I have

SELECT PartsandAccessories.PartID, PartsandAccessories.PartNumber FROM PartsandAccessories ORDER BY PartsandAccessories.PartNumber WHERE PartNumber = Forms.frmOrderParts.MFGID;

Does my MFGID Box need to be unbound or something? I think i am Supposed to change the from Categories in the first part of my code?

and once again I can thank you enough I know it hard explaining this stuff to people like me.

Antonio,
Where is the Categories table? From your list it looks like you have a table named Vendors and the PK is MFGID and it has a field named Name.

Assuming you want the name and ID from the Vendors Table in your combo box, try changing your code below:
    "strSQL = "Select" & Me!PKMFGID
    strSQL = strSQL & "from Categories" "

TO: strSQL = "SELECT MFGID, Name FROM Vendors "

That should get you both the PK and the Name of the vendor in the combobox. (Be sure the combo boxes property sheet is set to columns = 2, columnwidth should have 2 numbers with a semi-colon after each. Use "0" if you do not want the column to show, Bound Column = 1


For the Row Source for PartID, you have the clauses in the wrong order:

SELECT PartsandAccessories.PartID, PartsandAccessories.PartNumber FROM PartsandAccessories ORDER BY PartsandAccessories.PartNumber WHERE PartNumber = Forms.frmOrderParts.MFGID;

Try:

"SELECT partid, partnumber FROM partsandAccessories WHERE partnumber = " & partnumber & " ORDER BY partnumber"

Watch those spaces in the SQLs. Also, if you are drawing your recordset from only one table, you do not need to preface each field with the table name (it is listed in the FROM clause.) BTW- Where is your PartsAndAccessories table?

HTH,

Loralee