My code doesn't do what I want it to do (1 Viewer)

[Divit11]

My code doesn't do what I want it to do (Still unresolved)

Hello All,

I'm trying to display a choosen record from a table with a record id that is a TEXT data type. A variation of this same code works correctly in another form where the record source is a table with a record id that is a AUTONUMBER data type.

The below code (Access 2000) displays the first record in the table.

Private Sub cmdTypeCodeSearch_Click()
On Error Resume Next

Dim strtyTypeCodeID As String
strtyTypeCodeID = GettyTypeCodeID

'Just put this in to verify record id
MsgBox "You selected " & strtyTypeCodeID & " for your record key"

If Not IsNull(strtyTypeCodeID) Then
Me.RecordsetClone.FindFirst "[tyTypeCodeID] = " & strtyTypeCodeID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Record Not Found"
End If
Me.Refresh
End If

End Sub

Any guidance would be appreciated.

Dale

 

[selenau837]

Hello All,

I'm trying to display a choosen record from a table with a record id that is a TEXT data type. A variation of this same code works correctly in another form where the record source is a table with a record id that is a AUTONUMBER data type.

The below code (Access 2000) displays the first record in the table.

Private Sub cmdTypeCodeSearch_Click()
On Error Resume Next

Dim strtyTypeCodeID As String
strtyTypeCodeID = GettyTypeCodeID

'Just put this in to verify record id
MsgBox "You selected " & strtyTypeCodeID & " for your record key"

If Not IsNull(strtyTypeCodeID) Then
Me.RecordsetClone.FindFirst "[tyTypeCodeID] = " & strtyTypeCodeID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Record Not Found"
End If
Me.Refresh
End If

End Sub

Any guidance would be appreciated.

Dale

See the part of the code I have bolded. Where is that at? Is that a name of a text box? Is that calling upon another class or function? If it is, then that may be the problem. That get may be set up for a int and not a string.

 

[Rich]

Me.RecordsetClone.FindFirst "[tyTypeCodeID] = " & " ' " & strtyTypeCodeID & " ' "

 

[Divit11]

Hi Selena,

That get statement is located in my basSearch Module a portion of which is here:


Option Compare Database 'Use database order for string comparisons
Option Explicit

Public lngpcIDSelect As Long
Public lngmlIDSelect As Long
Public strtyTypeCodeIDSelect As String

Public Function GettyTypeCodeID() As String
On Error GoTo Err_GettyTypeCodeID
strtyTypeCodeIDSelect = "BLANK"
DoCmd.OpenForm "frmTypeCodeSearch"
Do While strtyTypeCodeIDSelect = "BLANK"
DoEvents
Loop

If strtyTypeCodeIDSelect = "BLANK" Then
strtyTypeCodeIDSelect = ""
End If
GettyTypeCodeID = strtyTypeCodeIDSelect
Exit Function

Err_GettyTypeCodeID:
MsgBox "Error number " & Err.Number & ": " & Err.Description
Exit Function

End Function


The value that shows up in my temp msgbox *seems* to be the correct string of characters.

Dale

 

[Divit11]

Hi Rich,

Your solution generates the "Record Not Found" branch of the if then statement.

Dale

 

[Divit11]

Still fishing for some ideas.

Dale

 

[RuralGuy]

Try:
Me.RecordsetClone.FindFirst "[tyTypeCodeID] = " & Chr(34) & strtyTypeCodeID & Chr(34)

 

[Divit11]

Problem solved

Thanks RG,
I dropped that line in and my form now displays the proper record. That's TWICE this week that you have been able to get me straight. Thanks Again.

Dale

 

[RuralGuy]

Glad to assist Dale. Thanks for posting back with your success.