Using a combo Box to select a record. 4

[jasonmac]

Hi Everyone. I have a form the has these fields:

PartNum
Tool-code
Rev-code
PcsPerCtn

I want to use a combo box to search for a specific record. In the combo box, as of now, there are three columns. PartNum, Tool-code, and Rev-code. This is because only through a combination of the three of them can you be sure you have the right record. For example, two records may have the same partNum and tool-code but have a different rev-code. I am trying to use this code in the after update of the combo box:

       Dim Rec As Recordset
    Set Rec = Me.RecordsetClone
    Rec.FindFirst "[PartNum] =" & vbCrLf _
        & Me![cboPartNum] & vbCrLf
        
    Me.Bookmark = Rec.Bookmark

I'm not getting an error but the fields are not updating. Is this because I'm just looking at the part number and there can be several records with the same part number?? Will I have to break this into 3 combo boxes? Can anyone point me in the right direction?

Thanks in Advance,
Jason

 

[scriverb]

Give this code a try. You are not finding an individual record because you are not searching for all three field values which make it a unique record. I have written this for Numeric field for PartNum and Alphanumeric for both Tool-Code and Rev-Code. If that is not correct then let me know and we can adjust it. You see the carriage return was not necessary in the code just the cancatenation and a underscore to continue the string.

Dim Rec As Recordset
Set Rec = Me.RecordsetClone
Rec.FindFirst "[PartNum] = " & Me![cboPartNum] & _
"[Tool-Code] = '" & Me![cboPartNum].column(1) & "' " & _
"[Rev-Code] = '" & Me![cboPartNum].column(2) & "'"
Me.Bookmark = Rec.Bookmark

The combobox cboPartNum should have three columns with column 1 being the bound column. the column widths should be sufficient to see the data for all three when the dropdown is visible.

Let me know if this starts to work for you now.


Bob Scriver

 

[jasonmac]

That looks like just what I'm looking for but the PartNum field is text and I'm getting an error here:

Rec.FindFirst "[PartNum] = " & Me![cboPartNum] & _
"[Tool-Code] = '" & Me![cboPartNum].column(1) & "' " & _
"[Rev-Code] = '" & Me![cboPartNum].column(2) & "'"

That there is a missing operator.

Thanks Again,
Jason

 

[WildHare]

Missing operator is a couple of quotes and a couple of ANDs...


Rec.FindFirst "[PartNum] = '" & Me![cboPartNum] & _
"' and [Tool-Code] = '" & Me![cboPartNum].column(1) & "' " & _
"and [Rev-Code] = '" & Me![cboPartNum].column(2) & "'"

See 'em in there?...





Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[jasonmac]

That's working just fine. Thanks to both of you. If anyone has the time to explain why all the quotes and "and's" are necessary I sure would appreciate it. Just so when this comes up in another application I will know what I'm doing.

 

[scriverb]

Thanks WildHare. Must be one of those "Bad Hair(hare)" moments. Left those AND's out. Pseudo code. I love it.

Bob Scriver

 

[scriverb]

As you build a string expression you are using double-quote marks to identify the strings to be included. When you are comparing a text field to a string value as in all three of your fields the comparison must have quotes around the comparison string. Thus the use of the single quotes around the data from the columns of the combobox. In the end the expression looks something like this:

"[PartNum] = 'A123z' and [Tool-Code] = 'AA45C' and [Rev-Code] = 'gh234v'"

This expression can now be executed by ACCESS as it knows what data is to be compared. If the single quotes were left out then a type-mismatch would have occurred as the Text fields would have been compared to a numeric value. The single quotes around the data designate is as a text string.

The ANDs mean that all three comparisons of data must be equal for the record to be found.

I hope this helps you understand what we have done here.

Bob Scriver

 

[jasonmac]

Thanks, scriverb. That was everything I needed to know. I'll keep a hold of this for future reference.

 

[jasonmac]

I have come across a new problem while using this method in another application. I am getting an error when the first field I use is a date field formatted to Short Date. The error is: "Data type mismatch in criteria expression."

The code works properly when I change the Date field in my table to text. Is there a way to get this to work while keeping the date field?

Thanks
Jason

 

[scriverb]

Surround your date variable which is probably a text string with # signs(i.e. [DateField] = #06/02/2003#)

If this doesn do it just post your code.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jasonmac]

I'm not sure exactly where to place the #'s. Here is the code:

Private Sub cboPartNumber_Change()
Dim Rec As Recordset
Set Rec = Me.RecordsetClone
Rec.FindFirst "[Date]='" & Me![cboPartNumber] & _
"'and [PartNumber]='" & Me![cboPartNumber].Column(1)&"' "& _
"and [PO]='" & Me![cboPartNumber].Column(2) & "'"
Me.Bookmark = Rec.Bookmark

End Sub

 

[scriverb]

What I am concerned about here is that you trying to compare the Date field to a combobox named PartNumber. Is the bound column for the combobox a date? If it is that's okay but if not then we should be looking at that issue. Otherwise, the following # signs around the datevalue string coming from the combobox control should work for you:

Private Sub cboPartNumber_Change()
Dim Rec As Recordset
Set Rec = Me.RecordsetClone
Rec.FindFirst "[Date]= #" & Me![cboPartNumber] & _
"# and [PartNumber]='" & Me![cboPartNumber].Column(1)&"' "& _
"and [PO]='" & Me![cboPartNumber].Column(2) & "'"
Me.Bookmark = Rec.Bookmark

Let me know if this works now for you.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jasonmac]

That did the trick.
Thanks Again!

 

[jasonmac]

I tried to use this method again with an integer in the place of the date and it is giving me the same error. What do I have to do to get it accept a field called EventID which is an auto number.

Can you recommend a place to look up the syntax for all for there different data types. This is becoming exceedingly frustrating.

Thanks again,
Jason

 

[scriverb]

This is how you are trying to make the following expression to look like given the data type. You must employ the cancatenation techniques with the ampersand(&) to "GLUE" all of the strings and data parts together:

Dates:
tblYourTableName.[DateFieldName] = #06/02/2003#

"tblYourTableName.[DateFieldName = #" & Date() & "#"

TextStrings:
tblYourTableName.[TextFieldName] = 'Smith'

&quot;tblYourTableName.[TextFieldName] = '&quot; & <Control or Variable Name> & &quot;'&quot;

Numeric:
tblYourTableName.[NumericFieldName] = 12345

tblYourTableName.[NumericFieldName] = & <Control or Variable Name>

This should give you and idea how to handle the different situations.



Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jasonmac]

Thanks again Bob. You've been a great help.

Jason

 

[tecassitant]

i have a dought like if i write
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst &quot;[STOCK_NO] = '&quot; & Me![List54] & &quot;'&quot;
Me.Bookmark = rs.Bookmark
i have a set of fields like pono,shipmt,LT1 along with stock_no if i select stock_no from a combo it is giving that particular stock_no at thr first in the form but it is giving all the other stock_nos preceeding that why it is so i want only the records for the stock_no i haveselected from the combo any help