Cascading combo boxes... 2

[JasonPurdueEE]

hello everybody. got a question about making a form in Access 97 for ya. What I need to do is come up with a series of cascading combo boxes to allow users to pick different combinations from the dropdowns. the contents of the second dropdown need to be based off of the selection of the first combo box and the third dropdown needs to be based off of the selection of the first and second combo boxes, and so on and so forth. anybody know how to do this? Thanks in advance.

JASON

 

[FoxProProgrammer]

You can tell if the Form is bound to the table by looking in the control source property for the Form. If a table is selected, then the form is bound.

You would place the code to populate the text boxes in the OnClick event of the combo boxes. One potential problem that you may not have addressed is if the user clicks the combo boxes in a different order than combo 1 and then combo 2. What would happen for example, if they click combo box 1, combo box 2, and then go back to combo box 1? You will need to make sure that your code handles this, which is why I suggested putting the code to populate the bound fields in the OnClick procedures of both combo boxes. If they select something in combo 1, I would suggest initializing combo 2 to the first row, and populating the bound fields for that record.

dz

 

[JasonPurdueEE]

alright, this is good. I'm so close now I can taste it! thank you. if I wanted to alter the above code so it would work if VWOP and WorkPackage were in seperate fields, what would I have to do? could I nest the RecordsetClone commands or something?

 

[JasonPurdueEE]

also, how does the RecordsetClone work in the text box (yes, I checked out the help files, no they werent any help). will I create several text boxes for each field in the table or exactly what will I have to do?

 

[FoxProProgrammer]

Jason,

I don't understand your questions. VWOP and WorkPackage are separate fields, no? I'm not sure what you are asking about the RecordsetClone and creating several text fields. Please provide more details. If you are asking how the code that I suggested works, a RecordsetClone can be viewed as a copy of the form's control source. For example, if your form is bound to the VarianceCommentary table, the RecordsetClone would have the same structure and data as the VarianceCommentary table. When you do a FindFirst on the RecordsetClone, you are essentially searching the table, through the clone, for a record that matches the criteria in the expression. If a match is found, the record pointer is positioned on that record. You then have to tell Access to retrieve the data for that record, which is essentially what the Bookmark = RecordsetClone.Bookmark statement does. Every record has a unique bookmark, and Access keeps track of them for you. If you want to position the record pointer of your form on a specific record in the table (RecordsetClone in this case), you set the form's bookmark to the recordset's bookmark.

dz

 

[JasonPurdueEE]

yes, VWOP and WorkPackages are seperate fields. I'm sorry for the incoherant questions, as I'm slowly figuring this out I see how dumb some of the questions are. please excuse my Access n00bness.

I guess my question now is, how do I set up my form to display the rest of the record? if I have 5 more fields to a record, do I place 5 text boxes on my form and bound them to the VarianceCommentary table? or how does that work?

I am getting the "Record not found" error. so I know theres something wrong with either how I set the OnClick code (from above) or how everything is bound. do you have any suggestions on what I could look for?

 

[FoxProProgrammer]

Access populates bound fields automatically when you set the bookmark for the form to the bookmark of the recordset. If you can use bound fields, that's the easiest way to go. However, when you use bound fields, changes that the user makes to controls on a form are automatically saved in the table unless you turn that option off by setting the appropriate property. Just create a text box for each of the other fields in your table and make them bound to the table.

If you are getting the Record not found error, then I would suspect that the expression in your FindFirst isn't correct. The best way to try to figure out what is wrong is by setting a break point and seeing what the value of expression is when it executes. Please post your FindFirst...code and I'll see if anything stands out.

And don't be so hard on yourself. No question is dumb. It is just hard to communicate through cyberspace sometimes and I didn't understand what you were asking.

Best,

dz

 

[JasonPurdueEE]

here is the findfirst statement which resides in my OnClick for text box PWR.
**********************************

Private Sub PWR_Click()

RecordsetClone.FindFirst "VarianceCommentary.VWOP = " & Trim(Str(VWOP.Value)) & " And VarianceCommentary.WorkPackage = " & Trim(Str(WorkPackage.Value))

If RecordsetClone.NoMatch = True Then
MsgBox "Record not found." ' this is the error I get
Else
Bookmark = RecordsetClone.Bookmark
End If

RecordsetClone.Close

End Sub

 

[FoxProProgrammer]

Try this:

RecordsetClone.FindFirst "VWOP = " & Trim(Str(VWOP.Value)) & " And WorkPackage = " & Trim(Str(WorkPackage.Value))

VWOP and WorkPackage have to be numeric data for this to work. If they are strings, use the following code instead:

RecordsetClone.FindFirst "VWOP = " & Chr(34) & VWOP.Value & Chr(34) & " And WorkPackage = " & Chr(34) & WorkPackage.Value & Chr(34)

You might consider changing the names of your textbox controls to avoid a conflict with the field names in the table. I'd suggest txtVWOP and txtWorkPackage.

dz

 

[gol4]

FoxProProgrammer,
I gave you a star just for the patience you have shown on this one. 10 days and 27 threads is far more then I could have handled.

 

[JasonPurdueEE]

ok, I gave that a try and changed the name of my textbox controls. now the PWR text box comes up with nothing, but I'm not getting an error either. I really do appricete your help.

 

[FoxProProgrammer]

lol, gol4. Thanks!

Jason, An email is heading your way.

dz

 

[JasonPurdueEE]

thanks for all your help.

 

[TrtentisAK]

I have a nearly identical problem to JasonPudueEE, and have got it about solved. I am cascading through six fields, and the above procedures work great. HOWEVER, In many instances, after two or three boxes, the recordset is already limited to one record. Any suggestions?

Also, I am using Access 2k, and would like to try to use ADO instead of DAO, as the above suggest. Any clues?