Solved MS office cannot find the field ... referred to in your expression. (1 Viewer)

[MaryamF]

I need and appreciate your help.

Running this program I get the error message:
MS office cannot find the field ... referred to in your expression.
Highlight stands on the red line.

I have a table :TblePeople , a form with a textbox which gets TenantSSN. if it exists in the table, a massage says: This Person Already exists! and three other textboxs are supposed to get the Name, Phone number and date of birth out of the TblePeople .
otherwise nothing happens and the user will fill the three boxes.

Private Sub SSNumber_Exit(Cancel As Integer)
Dim dbs As DAO.Database
Dim rstRecordTrans As DAO.Recordset

Set dbs = CurrentDb
Set rstRecordTrans = dbs.OpenRecordset("TblePeople")
Dim PName As String
Dim PDOB As Date
Dim PPhone As Integer


Me.[SSN] = Me.[SSNumber]
If DCount("*", "TblePeople", "SSN='" & Me.TenantSSN & "' ") > 0 Then MsgBox "This Person Already exists!", vbExclamation


PName = [TblePeople]![FullName].Value
PDOB = [TblePeople]![PhoneNumber].Value
PPhone = [TblePeople]![DOB].Value

Me.TenantName = PName
Me.TenantPhone = PDOB
Me.TenantDOB = PPhone


rstRecordTrans.Close

End Sub

 

[Pat Hartman]

It is not clear whether you are talking about two tables or one. The same data does NOT belong in multiple places.

If you use an unbound combo to search for TenantSSN, the combo can filter the form so that the related person record shows up and populates the form. If no match is found, then the user would enter all the required fields and save the record. Don't forget to add the validation code to the form's BeforeUpdate event to ensure that all required fields have been entered and also that the value they typed into the bound TenantSSN control does not already exist.

 

[ebs17]

About technology

PName = [TblePeople]![FullName].Value

So you can't read a value from a table.
But you correctly created a recordset. This is ideal for reading values.

With rstRecordTrans
    PName = !FullName
    PDOB = .Fields("DOB")      ' different spelling
    PPhone = .Fields("PhoneNumber")
End With

With a normal bound form you save yourself the tedious data transfer tab field => recordset => variable => form field

 

[The_Doc_Man]

One problem I see here is that you do not navigate within the recordset. Another is that you determine the count of matching records and if not 0, you give the message - but then you would go on and enter the record anyway, even if you have a potential duplicate. But since you don't navigate, that means you would be overwriting the first record of the recordset, whatever it happens to be. And because you used the domain aggregate for a lookup, that means you have NO CLUE as to WHICH record you just overwrite, because the domain aggregate function doesn't affect the position of the recordset.

Assuming you really HAVE to do it this way, you need to look up the recordset.FindFirst and recordset.AddNew actions. Here are the links to one possible set of reference articles.

Recordset.AddNew method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

Recordset.FindFirst method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

However, the advice of using a bound form is good. It would help you a lot by doing some things automatically for you. AND on a bound form you can use the query wizard to look for a particular record if that is what you needed to do.

 

[MaryamF]

I get error on this line :
operation is not supported for this type of object

rst.FindFirst "[SSN] LIKE '" & Me.SSNumber & "'"

 

[MaryamF]

Thank you.
I don't get the error anymore.
But the record I got from the table is the first record, not the one I want.
I wrote a findfirst:
rst.FindFirst "[SSN] LIKE '" & Me.SSNumber & "'"

I get this error:
operation is not supported for this type of object.

 

[plog]

I have a table :TblePeople , a form with a textbox which gets TenantSSN. if it exists in the table, a massage says: This Person Already exists! and three other textboxs are supposed to get the Name, Phone number and date of birth out of the TblePeople .
otherwise nothing happens and the user will fill the three boxes.

Is this a bound form? Are you doing an additional check upon submission? Bound forms are either in Add mode or Edit mode and if this is a bound form in Add mode you're still allowing duplicates, but making them more consistent.

I wouldn't use this method you are trying. I would put a form before this form and make users enter the SSN there--if it's a dupe open up your form to the existing SSN, if not a dupe open up the form to a blank record and carry over the SSN.

Lastly, if it's not a dupe you're code is going to break here:

Me.TenantName = PName
Me.TenantPhone = PDOB
Me.TenantDOB = PPhone

Those variables aren't set if no dupe, and they are not inside an IIF statement that binds them to just executing if a dupe is found.

 

[The_Doc_Man]

If you wrote

rst.FindFirst "[SSN] LIKE '" & Me.SSNumber & "'"

in the context of the routine you first showed us, the recordset object's name doesn't match up. Therefore, "rst" isn't a recordset and certainly isn't an open recordset.

I'll lay odds you don't have "Option Explicit" at the top of your module declaration, because if you DID have that, the line you quoted would not have compiled correctly.

I also detect what appears to be confusion about the name of the field being searched. Is it really SSN or is it SSNumber or is it TenantSSN? Access VBA really needs naming to be exactly right. If it isn't, you get all kinds of strange behavior.

 

[Pat Hartman]

rst.FindFirst "[SSN] LIKE '" & Me.SSNumber & "'"

You have not yet explained whether Me.SSNumber is bound or not. You CANNOT use bound controls as search criteria.

 

[arnelgp]

use BeforeUpdate event, not Exit event. You only validate the SSNumber when you Change it and not everytime you Exit the control.
Also, you can see that there is Cancel parameter on the sub, it means you are in validation sub, so Avoid assigning values to your form.
Let validation code finished before you can assign any values to your control.

Private Sub SSNumber_BeforeUpdate(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim rstRecordTrans As DAO.Recordset

    Set dbs = CurrentDb
    Set rstRecordTrans = dbs.OpenRecordset("TblePeople", dbOpenSnapshot, dbReadOnly)

    Me.[SSN] = Me.[SSNumber]
    
    With rstRecordTrans
        .FindFirst "SSN = '" & Me.SSNumber & "'"
        
        If Not .NoMatch Then
            Cancel = True
            MsgBox "SSS Number: " & Me.SSNumber & " belongs to:" & vbCrLf & _
            !FullName & " " & DOB & " " & !PhoneNumber, vbExclamation
        End If

        .Close
        
    End With
    
    Set rstRecordTrans = Nothing
    Set db = Nothing
    
End Sub

 

[MaryamF]

Thanks.
it works with no errors.
But,
I have an issue.
After I get the message that the SSN already exists, I get Full Name and DOB and Phone Number pop sup to textboxes and I want to continue working on other textboxes for this SSN.
But now, I have to change the SSN ....
the whole project is that :
Entering the SSN , if exists get 3 fields from the People table and add other data to another table. if doesn't exist entering all data to both tables.


Private Sub SSNumber_BeforeUpdate(Cancel As Integer)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TblePeople", dbOpenSnapshot, dbReadOnly)

Me.[SSN] = Me.[SSNumber]

With rst
.FindFirst "SSN = '" & Me.SSNumber & "'"

If Not .NoMatch Then
Cancel = True
MsgBox "SSS Number: " & Me.SSNumber & " belongs to:" & vbCrLf & _
!FullName & " " & !DOB & " " & !PhoneNumber, vbExclamation
Me.TenantName = !FullName
Me.TenantName.Enabled = False
Me.FullName = !FullName
Me.FullName.Enabled = False
Me.TenantDOB = !DOB
Me.TenantDOB.Enabled = False
Me.DOB = !DOB
Me.DOB.Enabled = False
Me.TenantPhone = !PhoneNumber
Me.TenantPhone.Enabled = False
Me.PhoneNumber = !PhoneNumber
Me.PhoneNumber.Enabled = False

End If

.Close

End With

Set rst = Nothing
Set dbs = Nothing

End Sub

 

[arnelgp]

you remove the line:

Cancel = True

if that is what you want.

 

[MaryamF]

you remove the line:

Cancel = True

if that is what you want.

Where should I put this line:
Cancel = True

 

[Pat Hartman]

I can't figure out why you are opening a recordset to find a record and if you find it, copy it to the current form's controls? what is the purpose of duplicating the data?

Also, using SSN as a PK is seriously poor practice. it should be just a data field and should usually be hidden on forms.

 

[arnelgp]

copy this and replace your code:

Dim m_SSS As String

Private Sub Form_Timer()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    ' immediately kill the timer
    Me.TimerInterval = 0
   
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("TblePeople", dbOpenSnapshot, dbReadOnly)

    With rst
        .FindFirst "SSN = '" & m_SSS & "'"
       
        Me.TenantName = !FullName
        Me.TenantName.Enabled = False
        Me.FullName = !FullName
        Me.FullName.Enabled = False
        Me.TenantDOB = !DOB
        Me.TenantDOB.Enabled = False
        Me.DOB = !DOB
        Me.DOB.Enabled = False
        Me.TenantPhone = !PhoneNumber
        Me.TenantPhone.Enabled = False
        Me.PhoneNumber = !PhoneNumber
        Me.PhoneNumber.Enabled = False
        .Close
    End With
    Set rst = Nothing
    Set db = Nothing
   
End Sub

Private Sub SSNumber_BeforeUpdate(Cancel As Integer)

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("TblePeople", dbOpenSnapshot, dbReadOnly)

    Me.[SSN] = Me.[SSNumber]
    m_SSS = Me.[SSNumber]
   
    With rst
        .FindFirst "SSN = '" & Me.SSNumber & "'"

        If Not .NoMatch Then

            MsgBox "SSS Number: " & Me.SSNumber & " belongs to:" & vbCrLf & _
                !FullName & " " & !DOB & " " & !PhoneNumber, vbExclamation

            ' enable the timer event
            Me.TimerIntervals = 100

        Else
            Me.TenantName.Enabled = True
            Me.FullName.Enabled = True
            Me.TenantDOB.Enabled = True
            Me.DOB.Enabled = True
            Me.TenantPhone.Enabled = True
            Me.PhoneNumber.Enabled = True
       

        End If

        .Close

    End With

    Set rst = Nothing
    Set dbs = Nothing

End Sub