Dependent Combo Boxes Question 2

[smille777]

Hi everybody.

I have tried reading through the relative posts, but am not nearly experienced enough to figure this out:

I have a "Vendor Quotes" Form which contain two Combo Boxes: 1) Customer 2) MFR Part Number

The Combo boxes are based on the following tables with a 1 to many relationship:

"Customer Requiremsnts" and Customer Requirement Details"

Relative fields for "Customer Requirements" are:

[Customer Req ID], [Customer]

Relative fields for "Customer Requirement Details" are:

[Customer Requirement Details ID], [Customer Req ID], [MFR Part Number], [Manufacturer]

I want to select [Customer] from Combo Box 1 and bring bring up [MFR Part Number] in Combo Box 2, based on [Customer]

I know I probaly made this long and drawn out, I appreciate your help everyone!!!

Scott

 

[scriverb]

Use the following SQL for the RowSource for the first combobox:

Select A.[Customer Req ID], A.[Customer] 
FROM [Customer Requirements] as A 
ORDER BY A.[Customer];

Setup the combo properties as follows: Columns 2, Bound Column 1, Column Widths 0;2

Use the following SQL for the RowSource for the Second combobox:

Select A.[MFR Part Number] 
FROM [Customer Requirement Details] as A 
WHERE A.[Customer Req ID] = FORMS![[I][RED]yourformname[/red][/i]]![[i][red]combobox1[/red][/i]] 
ORDER BY A.[MFR Part Number];

Setup for this combo properties are as follows: Columns 1, Bound Column 1, Column Widths 1

The AfterUpdate event procedure of the ComboBox1 should be as follows:

Me![combobox2].Requery
Me![combobox2].setfocus
Me![Combobox2].dropdown

Now we may have to adjust a few of the columns to display as you need but the basics are here. The query SQL for the Second combobox is making a criteria reference back to the forms combobox1. Depending upon the datatype of the field we may have to add some quotes around the controls value for it to work.

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

Bob, thank you so much for the reply.

I tried using the formula in faq702-4289, but received: "Syntax error (missing operator)in query expression 'tblCustomer_Req's.Customer ReqID"

The formula was written as follows:

CboCustomer
RowSource: SELECT tblCustomer_Req’s.Customer ReqID, tblCustomer_Req’s.strCustomer
FROM tblCustomer_Req’s
ORDER BY tblCustomer_Req’s.strCustomer;

ColumnCount: 2

ColumnWidths: 0;2”

LimitToList: Yes

Events:

Private Sub cboCustomer_AfterUpdate()
cboMFR_Part_Number.Requery
End Sub

CboMFR_Part_Number
RowSource: SELECT tblCustomer_Req_Details.lngCustomer_Req_DetailsId, tblCustomer_Req Details.strMFR Part Number, tblCustomer_Req_Details.lngCustomer_ReqId
FROM tblCustomer_Req_Details
WHERE (((tblCustomer_Req_Details.lngCustomer_ReqId)=[Forms]![Vendor_Quotes]![cboCustomer]))
ORDER BY tblCustomer_Req_Details.strMFR_Part_Number;

ColumnCount: 3

ColumnWidths: 0;2”;0

LimitToList: Yes

Events:
Private Sub cboMFR_Part_Number_GotFocus()
If Len(Trim(Nz(cboCustomer, "") & "")) = 0 Then
MsgBox "Please Specify Customer first"
cboCustomer.SetFocus
Else
cboMFR_Part_Number.Requery
End If
End Sub


IWILL TRY YOUR APPROACH AND SEE IF I CAN GET IT TO WORK..

THANKS, SCOTT

 

[scriverb]

Very similar approach but the problem is the apostrophe in the name of the table. Can't have that. ACCESS cannot interpret the name properly. It is looking for an expression operator as if there is a string variable someplace.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

Bob, you are great!! First, your code is, without question, much simpler, which at least for my needs, is always a beter approach.

I was able to get your codes to work. Now, my table is named "Customer Req's" rather than "Customer Requirements" as I first posted. I will rename.

Now, when you have a moment, I have one more issue.

In Customer Req Details I have the following fields in this order: [Customer Req Details ID] [Customer Req ID] [MFR Part Number] [Manufacturer] [Customer Part #] [Qty] [Target] [D/C Required]

The combo boxes work, but once I slect the appropriate [MFR Part Number] from combo box 2 (cboMFR Part Number), how do I auto populate [Manufacturer] [Qty] [Target]??


Thank you, so much, with all your help. There is a lot more for me to learn, and I appreciate your help!!
Scott

 

[scriverb]

Include them in your select SQL for the RowSource. Increase your Column count to include the total number of columns in the Select. In the column widths include them as a 0" column so they do not display in the dropdown. Now in the AfterUpdate of the second combo you can assign them to form controls by referring to them using the .column property of the combobox. The numbering starts at 0 for column 1.

Me.TextBox1 = Me.comobobox2.column(2)
Me.TextBox2 = Me.comobobox2.column(3)
Me.TextBox3 = Me.comobobox2.column(4)
Me.TextBox4 = Me.comobobox2.column(5)

The above is just an example but we would be assigning the actual columns 3 thru 6 to the textboxes. If you need help with the actual coding just post back I will help you.

Thanks for the star and good luck with your project.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

Bob,

Thanks for the codes for the auto populate. I actually had these codes written in after update, but it turns that,I did not update some name changes I had done earlier, so this was the reason I could not get them to work.

I do have one more question:

How can I get cboMFR Part Number to list
[Manufacturer] [Customer Part #] [Qty] [Target]in the drop down, as well?

Thanks again for all your help and great pastience. This is exactly what makes a great TEACHER.

Scott

 

[scriverb]

Include the fields in your SQL and then include a value corresponding to the columns in the column widths. Instead of 0 put in a value in inches or decimal equivalent.

Just an example:

0;2;.5;3

Now the items will showup in the dropdown. Remember that the first item in the list is the one that when the user begins typing the search begins looking for that particular record. It is for this reason that I usually include the ID field as the first one and declare it is the bound field. Then the field that is being searched numerically or alphabetically by the user is next. All of the others are then just there for display and aide in selection of the correct record. You see the ID field really doesn't mean anything except to the programmer yourself. You use this value to link and find records but the user doesn't really need to see or deal with it. It is just confushing to them.

I hope this helps clear this up for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

PERFECTLY CLEAR.

I think I have been working so much on this and, having just started using, I am learning a lot fast. I forget r=things that I have already done or used before. You have been great Bob!

Thanks again! Scott

 

[scriverb]

I re-read my last posting I may not have been clear enough. The comobobox begins searching the first visible column. So, if ID is first but has a 0 width value, then it is ignored. It may be the bound column but the first visible column will be the one searched and ultimately displayed in the combo after the pick is completed. if you want two fields to be searched together in a particular order like Name then just cancatenate them together in the SQL like the following:

Search A.[ID], A.[LastName] & ", " & A.[FirstName] as Name FROM yourtablename as A ORDER BY A.[LastName] & ", " & A.[FirstName];

Now the second column can be the first visible column and a search is conducted alphabetically on both the last and first names making sure the user types in the ", ". Good luck with your project.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

Bob,

When adding these to SQL, does each new item begin with Select A. or is each new item assigned a different variable, ie B., C. ? What does represent?

I assure you I won't have any more questions after this!

Thanks again! Scott

 

[scriverb]

No A is just an Alias assigned to a query or table designation in the FROM clause of the query. It is shorthand so that you only have to type out the name of the table one time. Then from that point on just refer to it as A. The word Select is only used once but the A designation is used with each field name. Now you don't have to use A if there is only one table but when you join two or more tables then you should use the tablename or its alias so that you can keep the field straight as to where the data is coming from.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

Ok, I got it, FINALLY.

Row Source =

Select A.[MFR Part Number], A.[Manufacturer], A.[Qty Requested], A.[Target] FROM [Customer Req Details] as A WHERE A.[Customer Req ID] = FORMS![Vendor Quotes]![Customer]
ORDER BY A.[MFR Part Number], A.[Manufacturer], A.[Qty Requested], A.[Target];

Bound Column = 2
Column widths= 1.75";1.5";1";1"
List Rows=4
List Width=5.25"

After Update cboMFR Part Number =

Private Sub MFR_Part_Number_AfterUpdate()

Me![Manufacturer] = Me![MFR Part Number].Column(1)
Me![Qty Requested] = Me![MFR Part Number].Column(2)
Me![Target] = Me![MFR Part Number].Column(3)
End Sub


THANKS AGAIN BOB. NOW I SEE THE LIGHT AND IT IS WONDERUL!! THANK YOU, TOO MUCH!

SCOTT

 

[smille777]

Bob,

I thought I had it, but when I close the vendor Quotes form and then open it again, later, the [Customer] field is blank AND if I make an entry and create a new record [Customer] field is populated with the customer selected from previous record.

Also, when I go to the Vendor Quotes table, [MFR Part Number] is populated with [Manufacturer]

What ami Missing/done wrong?

Here are my codes:

cboCustomer

ROW SOURCE=
BOUND COLUMN= 1
COLUMN WIDTHS= 0;2

SELECT A.[Customer Req ID], A.Customer FROM [Customer Reqs ] AS A ORDER BY A.Customer;

Private Sub Customer_AfterUpdate()

Me![MFR Part Number].Requery
Me![MFR Part Number].SetFocus
Me![MFR Part Number].Dropdown

End Sub

cboMFR Part Number

Row Source =

Select A.[MFR Part Number], A.[Manufacturer], A.[Qty Requested], A.[Target] FROM [Customer Req Details] as A WHERE A.[Customer Req ID] = FORMS![Vendor Quotes]![Customer]
ORDER BY A.[MFR Part Number], A.[Manufacturer], A.[Qty Requested], A.[Target];

Bound Column = 2
Column widths= 1.75";1.5";1";1"
List Rows=4
List Width=5.25"

After Update cboMFR Part Number =

Private Sub MFR_Part_Number_AfterUpdate()

Me![Manufacturer] = Me![MFR Part Number].Column(1)
Me![Qty Requested] = Me![MFR Part Number].Column(2)
Me![Target] = Me![MFR Part Number].Column(3)
End Sub

In the meanwhile I am trying to do some research on SQL, to understand everything you have told me.

I look forward to your reply.

Thanks again! Scott

 

[scriverb]

Your Quotes form has a Record Source. So this form is considered a bound form. When a Bound Form is opened it is usually opened to the first record in the Record Source. If this is a table then it would be the first record. If you want to create a new record you must execute code to do so from behind a command button or you can use the navigation buttons at the bottom by clicking the Right Arrowhead with the Asterick. This gives you a blank record to begin entering data. All of your bound controls should now be blank.

Post back if this helps.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

Hey Bob,

I know about opening a new record and I have other forms with a macro that opens the form in "new record."

The combo boxes work, and after update works.

PROBLEM now is that given all of the codes I have listed above, I experience the following problems:

FORM PROBLEMS:

1) Let's say I begin with "RECORD 1" in Vendor Quotes and enter a customer in cboCustomer and then select info from cboMFR Part Number. The fields fill in and after update populates [Manufacturer], [Qty], [Target] as needed.

BUT, WHEN I create new record, let's say "RECORD 2" the cboCustomer is already populated with [Customer] from previous record, "RECORD 1." ALSO, if I select a different Customer, in "RECORD 2" and complete the fields, WHEN I go back to "RECORD 1", then cboCustomer is populated with the Customer Selection from "RECORD 2" and
cboMFR Part Number is blank (basically, the data in the field is erased.

This is the primary problem (I hope I explained it clearly), but also [MFR Part Number] in tblVendor Quotes, which this form is bound to, contains [Manufacturer].

It's really messed up.

I really don't want to keep bothering you with this problem..but if you have any ideas, I would be hapy to hear.

Thanks again and again!

 

[scriverb]

The problem you have described is because your have not Bound the controls to the underlying Record Source. After you make a selection the value stays there until you Null the control value out. If there is a field in the underlying RecordSource that should be bound to the comboboxes after their pics are completed then select that field in the combobox's control source property. If either of them doesn't have an underlying field to be bound to but truly should be unbound then when you move to brand new record or to an existing record then you must use code to blank out the comboboxes.

Whatever buttons you have for next record, find record, or new record would have the code to null out the comboboxes.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

Bob,

cboCustomer is not, nor did I want it to be bound to the underlying table, while cboMFR Part Number is bound to the underlying table.

Would I only place the code to clear the combo box for the next record in cboCustomer?

If so, what is the code that should be placed in there?
Also, the "new record" macro is NOT used in this form.

Thanks for yor help!

Scott

 

[scriverb]

Yes, you should only put the code to clear your cboCustomer combo. The other one will return the value from the underlying table or a null value on a new record.

Put the following code in the OnCurrent event procedure:

Me![cboCustomer] = Null

This code will clear the cboCustomer on a new record or when you move to a different existing record.

Hope this works for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[smille777]

Thanks Bob.

This thread is starting to look like the longest one I've seen between two users.

I have learned a lot from you.

Everything works perfectly except for ONE thing:

As far as the Vendor Quotes Form and its fields are concerned, everything works great. But, when I view Vendor Quotes TABLE, the [MFR Part Number] Field contains the data entered for [Manufacturer]

Here is what I have:

CONTROL SOURCE =MFR Part Number

ROW SOURCE =

SELECT A.[MFR Part Number], A.Manufacturer, A.[Qty Requested], A.Target FROM [Customer Req Details] AS A WHERE A.[Customer Req ID]=FORMS![Vendor Quotes]!Customer ORDER BY A.[MFR Part Number], A.Manufacturer, A.[Qty Requested], A.Target;

BOUND COLUMN = 2
COLUMN WIDTHS = 1.75";1.25";0.9";0.75"


AFTER UPDATE =

Private Sub MFR_Part_Number_AfterUpdate()

Me![Manufacturer] = Me![MFR Part Number].Column(1)
Me![Qty Requested] = Me![MFR Part Number].Column(2)
Me![Target] = Me![MFR Part Number].Column(3)
Me![cboMFR Part Number] = Null

Thanks again for your help and this REALLY should be the last thing I need.

Scott