Combo box results differ

[rgreene]

In my db I an tracking different types of seed and the process from harvesting the seed, through all the stages of drying and so on all the way to being bagged and sold. I'm not tracking the sale other than it was sold. On my Events Form I have a subform on it. On the form portion I have a combo box that looks up the LOT ID for each seed. When a LOTID is chosen the subform populates with the events/stages that that seed has gone through. That portion works fine. Since the users don't always know the LOTID they would like to search by the PRODUCT (which is the seed name) then see the LOTID's that are available for that PRODUCT. It's basically a cascading combo box. This portion is working correctly. However after I have chosen my product then chosen the LOTID from the list of available LOTID's I want that to function just like my other lookup where I choose the LOTID and the events subform populates. So I copied the code from the LOTID LOOKUP to the same place on my cboLOTS. When I run my cascading combo I can choose the PRODUCT from there I get my list of available LOTID's and when I choose a LOTID I get the error "Run-Time error '13': Type mismatch"
My code is

Private Sub cboLots_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ADSID] = " & Str(Me![cboLots])
Me.Bookmark = rs.Bookmark

End Sub

The ADSID is an ID for each event/stage. I guess I don't know why this exact same code

Private Sub cboLotIDLU_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ADSID] = " & Str(Me![cboLotIDLU])
Me.Bookmark = rs.Bookmark
End Sub

will work when I'm choosing the LOTID directly but not when I use the cascading lookup?

Any suggestions,

Rick

 

[RuralGuy]

Is your cboLots control bound to the numeric field you want or is it bound to a string field? You should really be testing for success of the FindFirst with an If Not rs.EOF Then

 

[rgreene]

I was thinking it was unbound but if not then I'm assuming it's a string field because my LOTID is something like 02142005F18.

As far as testing the findfirst comment I have no idea what you are saying most of my coding is done by cutting and pasting existing code and trying to make it work. I actually had a fellow employee help me with the original lookup that's why I thought I could just cut and paste.

 

[RuralGuy]

Set rs = Me.Recordset.Clone
[COLOR="Red"]MsgBox "We'll look up ADSID = [" & Me.cboLots & "]"[/COLOR]
rs.FindFirst "[ADSID] = " & Me.cboLots
[COLOR="Blue"]If Not rs.EOF Then
   Me.Bookmark = rs.Bookmark
Else
   MsgBox "Unable to locate [" & Me.cboLots & "]"
End If[/COLOR]

Add the RED CODE as a diagnostic to see what you are asking FindFirst to locate. Then add the BLUE CODE to keep from setting the bookmark when the FindFirst fails.

BTW, the brackets [] and the coersion to Str() are not necessary.

 

[RuralGuy]

To add more to my explaination, a combo box has a bound column but the control might still be unbound. The bound column controls what is returned when you reference the .Value property. The RowSource of the cbo determines the order fields are displayed in the DropDown mode and from that you can determine what will be returned as the .Value.

 

[rgreene]

Ok I'm with you on the bound column. No my bound column is the ADSID column and my returned value comes is the LOTID


I added the code like you suggested and my cboLotIDlu still works fine and when I use the cascade or the cboLots I get the message it's looking it up but I don't get anything displayed in my subform section and there aren't any errors. So how do I get my events/stages to fill in?

Thanks for helping me with this!!

Rick

 

[RuralGuy]

I believe your LotID is a string value rather than numerical. Therefor you need to enclose it in quotes:
rs.FindFirst "[ADSID] = '" & Me.cboLots & "'"

 

[rgreene]

After making that change I get

Run-Time error '3464':
Data type mismatch in criteria expression

When I debug the line in blue is highlighted

Private Sub cboLots_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
MsgBox "We'll look up ADSID = [" & Me.cboLots & "]"
'rs.FindFirst "[ADSID] = " & Str(Me![cboLots])
rs.FindFirst "[ADSID] = '" & Me.cboLots & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Unable to locate [" & Me.cboLots & "]"
End If

End Sub

 

[RuralGuy]

Well, let's take the quotes back out. The error indicates that the [ADSID] field is numerical. At the very least you should get the "Unable to locate [" & Me.cboLots & "]" message and if the value is not all numeric then you are probably not selecting the proper column from the ComboBox.

 

[rgreene]

OK I've been playing around and I did notice that my ADSID field is a numeric (autonumber) field but my LotID is a text field. I don't know if that means anything. I'm still confused why it will work just fine using just a straight LotID lookup with the cboLotIDLU, but using the exact same code it doesn't after I get to the LotID using the cascading Combo boxes

As you have probably figured out by now I know VERY LITTLE when it comes to this. Does my Dim rs as Object need to be something else?

I'm back to your original suggestion and I'm back to a Run-Time error 13 Data type mismatch

I Really can't thank you enough for taking the time to help me out. I really wish I knew more it gets so frustrating

 

[RuralGuy]

I'm sorry to be so slow in responding but my internet connection has been sick all day. Can you post the SQL for the RowSource of the ComboBox?

 

[rgreene]

Rural Guy, I'm away from the database today, I'll get it potesed for you on Monday

 

[RuralGuy]

No problem. Have a great weekend.

 

[rgreene]

Here is the RowSource code for the nonworking section:
SELECT LotID FROM tblHarvest WHERE [Product]=4 ORDER BY LotID;


He is the RowSource from the working combobox:
SELECT tblHarvest.ADSID, tblHarvest.LotID FROM tblHarvest;


Rick

 

[RuralGuy]

I'm going to guess that the bound column of the second cbo is 1. Have you tried changing the RowSource of the first cbo to:

SELECT ADSID, LotID FROM tblHarvest WHERE [Product]=4 ORDER BY LotID;

and see what happens? Change the Column Count to 2 and set the first column width to 0"; to hide it.

 

[rgreene]

I tried that but from my understanding the RowSource changes and is entered based on my afterupdate event code. So when I changed the row source I got an message that popped up and then the code went back to the original I posted.

 

[RuralGuy]

Post the code that is changing the RowSource of that cbo.

 

[rgreene]

This is in the After update event

Set rs = Me.Recordset.Clone
rs.FindFirst "[ADSID] = " & Str(Me![cboLots])
Me.Bookmark = rs.Bookmark

 

[RuralGuy]

You are using cascading combo boxes right? What is the AfterUpdate event of the first cbo? Maybe now would be a good time to post your db after Toold>Database Utilities>Compact and Repair Database... and removing any sensitive data. The zipped up db needs to be < 394 KB.

 

[rgreene]

Correct. Here is the DB