FindRecord Help

[Novice1]

I have a form with a text box (SSNInput). I want the user to enter his or her SSN then click an icon to bring up data on that person. Note: The form data is not accessible to the user. I want the SSNInput to pull up the record with the SSN that's already in the table.

I'm trying to write a FindRecord command without success.

DoCmd.FindRecord , Me.SSN=Me.SSNInput, acEntire, , acSearchAll, , acAll

 

[Ranman256]

dont use FINDRECORD,
just open a query that uses the text box as criteria

docmd.openquery "qsFindSSN"
in the query is
select * from table where [ssn] = forms!frmFind!SSNinput

 

[The_Doc_Man]

You can also do a "wizard" for this, as there is a command button option to move the form to a selected input record. I'll explain why what you attempted did not work.

A recordset doesn't recognize a "Me." construct but "Me." as a shortcut DOES have a recordset. The syntax you used didn't work because you were attempting to move a form (which is what Me. means in that context) rather than a recordset. You would have to do something more along these lines:

me.recordset.findfirst "[SSN]=" & me.[SSNFind] & " "

But there are better ways to do this using Me.RecordsetClone for the perverse cases that would occur if the user was particularly fat-fingered.

I'm serious about this - use the wizard approach first, even though Access wizards are not the sharpest tacks in the drawer. What that would do is build the clunky code to do a .FindFirst for you and move your form to that record. Then maybe you can modify what the wizard wrote to do whatever else you might have wanted it to do along with the change of attention to the selected record. You can also search this forum for how to use the .RecordsetClone as a way to move your form's record to a specific selected record AND to protect against selecting a non-existent record due to ham-fingered typing.

And if anyone is offended by my comments about fat fingers - I've got as fat a set of fingers as anyone could imagine, so my humor is self-directed.

 

[Novice1]

Thanks for the reply. Still having a little trouble.

In the query, in criteria, I wrote...

=[forms]![frmCustomerInput1]![SSNinput]

but it's not pulling the data. Did I miss something?

 

[Novice1]

Receiving compile error (expected: case). What am I doing wrong?

DoCmd.OpenQuery "qrySGLICheck"
SELECT * FROM tblAlphaRoster1;
WHERE (([SSN] = Forms!customerinput1!SSNInput))

 

[Ranman256]

looks good,...OOPS, remove SEMI colon from roster1

and SSNInput is the TEXT BOX NAME? not the field, the name of the text box?
and [SSN] is the field name?
and customerinput1 is the form name?

 

[Novice1]

Yes, SSNInput is the text box name; [SSN] is the field name and customerinput1 is the form name.

DoCmd.OpenQuery "qrySGLICheck"
SELECT * FROM tblAlphaRoster1
WHERE ([SSN] = Forms!customerinput1!SSNInput)

I'm getting a compile error (syntax error).

 

[Ranman256]

Then all this is correct.
in the query, erase your criteria..forms!frm....

place cursor in the empty criteria under SSN
click the BUILDER icon on the tool bar
pick your text box under
database
forms
activeforms
customerinput1
SSNInput.

THEN NO WAY, can it give you a syntax error via the builder.

 

[Ranman256]

duplicate memo

 

[Novice1]

I'm still struggling with this one. I'm trying the Doc Man approach.

I want to retrieve one record. I have a text box (txtToGo). I placed the following code After Update. I'm getting a variable not defined error.

Private Sub txtToGo_AfterUpdate()

If (txtToGo & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[SSN]=""" & txtGoTo & """"
If rs.NoMatch Then
MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close
txtToGo = Null


End Sub

 

[vbaInet]

* txtToGo needs to be unbound
* if you use the After Update event of the textbox, the code will run after you move the cursor to another control

Based on The_Doc_Man's suggestions and the code you came up with:

Private Sub txtToGo_AfterUpdate()
    Dim rs As DAO.Recordset
    
    If Nz(Me.txtToGo, vbNullString) = vbNullString Then Exit Sub
    
    Set rs = Me.RecordsetClone
    rs.FindFirst "[SSN]=""" & Me.txtGoTo & """"
    
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & Me.txtGoTo & "' was found.", _
                vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    
    Set rs = Nothing
End Sub

 

[Novice1]

Still no luck. I'm not getting any errors. Instead it's not recognizing a valid SSN. I receive the not found message. Thinking it may have something to do with the format of the field (Format([SSN],"000-00-0000")), I tried adding the format to the code (no luck). Next, I tried using the code on a different field. The code pulled a record but not the record requested. I'm at my wits end.


Private Sub txtToGo_AfterUpdate()

Dim rs As DAO.Recordset

If Nz(Me.txtToGo, vbNullString) = vbNullString Then Exit Sub

Set rs = Me.RecordsetClone
rs.FindFirst "[SSN]=""" & Me.txtToGo & """"

If rs.NoMatch Then
MsgBox "Sorry, no such record '" & Me.txtToGo & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If

Set rs = Nothing

End Sub

 

[The_Doc_Man]

Note that this syntax is for a text-oriented SSN:

"[SSN]=""" & Me.txtToGo & """"

You can also write this as "[SSN]='" & me.txtToGo & "'" since SQL uses apostrophe as another type of quote marker. Might be clearer when you are reading it, though it makes no syntax difference worthy of note.

However, if [SSN] in the recordset is declared as a number, this would still fail. Text and numbers don't compare so well, and that syntax guarantees you will be doing text for the Me.txtToGo value.

The type of variable implied in a text box is a Variant (i.e. it can be pretty much anything) but [SSN] in a recordset will have an underlying table field that has a definite (and non-variant) type. If you put "000-00-0000" in an SSN text box on a form, the format is just about forced into text (because of the embedded dashes). This leads to the question: How is [SSN] declared in the place where it is first declared. If you use a query to include [SSN], it gets its data type from the original declaration, which is why I asked about the first declaration.

There is also some confusion in my mind about txtToGo. Is it actually Me![txtToGo] as a control on the form? Or was that a public variable in the form's declaration area? The dot syntax is somewhat ambiguous here. Me.X can mean public variable X declared in the declarations area of the form (even though that would be a class module for which the Public concept is a bit tenuous). By comparison, if you had MyGenModule with a public variable X in it, you would reference that externally as MyGenModule.X, so you can see there is at least a syntactic potential for confusion.