mud2
06-30-2009, 10:02 PM
Thanks for the help.
First: The suggested set rs = Me.Recordset.clone doesn't work, but plain Me.RecordSet does!
Second, a "work around". Who wants to struggle with all variations and permutations of single and double quotes?
I have a table (table1) containing case sensitive alphanumeric keys. A combo box (combo11) displays these keys. I chose a key from this box and want FindFirst to find the record with this key..the problem is findFirst is not case sensitive!
A good suggestion was to use Strcomp(), which leads to very complicated (for me) quotes and double quotes..sometimes!
The "Work round": is to add a field to the table that contains an "Automatic Number".
Next, Have the combobox contain BOTH this field and the key to be searched for, but do not display the key!(Set its width = 0).
Then, when a key is clicked, the automatic number is searched for. And keys such as Hello, Hello,HEllO, etc, all have UNIQUE automatic numbers! I call this number "crutch"
A simple vba is below:
In a Form,
The combo box is initiated in the forms Form activate:
With combo11
.RowSource = "Table1"
.Rowsourcetype = "Table/Query"
.ColumnCount = 2
.ColumnWidths = "0;1"
.value = ""
End With
Next, in the combo11.click sub:
Dim tempo as Integer
Dim rs as recordset
Set rs = Me.Recordset ' Not .clone!
tempo = Combo11.column(0) ' the column containing the automatic number
AND:
rs.FindFirst "[Crutch] = " & Tempo & ""
I found that ACCESS would not handle something like [Combo11.column(0)], so I simplified by using Tempo. At least I couldn't get it to!
Click on a key and the found record immediately becomes the record displayed in the form!
Now what will go wrong?
First: The suggested set rs = Me.Recordset.clone doesn't work, but plain Me.RecordSet does!
Second, a "work around". Who wants to struggle with all variations and permutations of single and double quotes?
I have a table (table1) containing case sensitive alphanumeric keys. A combo box (combo11) displays these keys. I chose a key from this box and want FindFirst to find the record with this key..the problem is findFirst is not case sensitive!
A good suggestion was to use Strcomp(), which leads to very complicated (for me) quotes and double quotes..sometimes!
The "Work round": is to add a field to the table that contains an "Automatic Number".
Next, Have the combobox contain BOTH this field and the key to be searched for, but do not display the key!(Set its width = 0).
Then, when a key is clicked, the automatic number is searched for. And keys such as Hello, Hello,HEllO, etc, all have UNIQUE automatic numbers! I call this number "crutch"
A simple vba is below:
In a Form,
The combo box is initiated in the forms Form activate:
With combo11
.RowSource = "Table1"
.Rowsourcetype = "Table/Query"
.ColumnCount = 2
.ColumnWidths = "0;1"
.value = ""
End With
Next, in the combo11.click sub:
Dim tempo as Integer
Dim rs as recordset
Set rs = Me.Recordset ' Not .clone!
tempo = Combo11.column(0) ' the column containing the automatic number
AND:
rs.FindFirst "[Crutch] = " & Tempo & ""
I found that ACCESS would not handle something like [Combo11.column(0)], so I simplified by using Tempo. At least I couldn't get it to!
Click on a key and the found record immediately becomes the record displayed in the form!
Now what will go wrong?