read cell contents

[brodkat]

Hello, I'm struggling up the steep VB learning curve and trying to write code that will "read" the contents of a cell in a form so that I can search another table for the same string. Hoping someone can read what I have and point out what I'm doing wrong.

The user uses the form to enter data. The code runs after they have entered an ID. My objective is to display a warning if what the user enters isn't in the 'other' table.

This is what I have so far - specific problems are embedded and bold:

Private Sub PointID_LostFocus()
Dim p As Integer
Dim currPt As String

currPt = "=[Forms]![Point_Count_DataEntry]![PointID]"
' this is supposed to read what the user just entered in the form
' Problem 1: I put a watch on currPt and the value doesn't change
' I would expect it to list exactly what the user just entered but it
' doesn't.

DoCmd.OpenTable "Points", acNormal, acReadOnly
DoCmd.GoToControl "PointID"
' open the 'other' table and go to the PointID field - this is working

p = DCount(currPt, "Points")
'supposed to count the number of records with the same PointID the user
' just entered. I also tried

![Points]![PointID] in place of "Points"
' both with and without quotes - all ways I get various syntax errors.
'Problem 2: I get a runtime error (3075) and it can't get past this line.
' it says there is a syntax error (missing operator) in query
' expression: 'Count(=[Forms...PointID])'. So refers back to the code ' in currPt. Maybe if I can fix that line this one will work...?

If p = 0 Then

Response = MsgBox("Please check the PointID. The ID you entered does not exist", , "Warning")

Else: DoCmd.Close acTable, "Points", acSaveNo

End If

End Sub

 

[John Big Booty]

Try;

Private Sub PointID_LostFocus()
Dim currPt As Integer [COLOR="SeaGreen"]'I'm guessing that PointID is a number rather than text here.[/COLOR]

currPt = Me.PointID

[COLOR="SeaGreen"]'There is no need to open these tables
'DoCmd.OpenTable "Points", acNormal, acReadOnly
'DoCmd.GoToControl "PointID"
' open the 'other' table and go to the PointID field - this is working[/COLOR]

If DCount("PointID", "Points", "PointID = " & currPt ) = 0 Then
     MsgBox("Please check the PointID. The ID you entered does not exist", , "Warning")
End If

End Sub

I'm presuming the the Points in your Dcount() is a table, this is where a naming protocol come in handy if you use something like TBL_Points it would be quite clear that is a table and not something else.

 

[brodkat]

Thanks big booty for your helpful response. Some of what you offered worked well but the DCount line still doesn't work because (a) currPt is a string (PointID is text format - e.g., MC-1234-2) and (b) I can't figure out the proper syntax for the criteria.

The code is mostly yours and currently looks like:

Private Sub PointID_LostFocus()
Dim currPt As String
Dim p As Integer
currPt = Me.PointID

'the line in question:
p = DCount("PointID", "Points", "PointID =" & "'currPt'")

'this is the only way I can avoid syntax or runtime errors but it isn't counting the 1 'record that IS equal to currPt

' By the way I declared 'p' so I can add it as a watch.

If p = 0 Then
Response = MsgBox("Please check the PointID. The ID you entered does not exist", , "Warning")
End If
End Sub

As written, p is always 0 but should be 1.

Any suggestions?

 

[pbaldy]

JBB appears to be offline. See here for the correct syntax for a text value:

http://www.mvps.org/access/general/gen0018.htm

Your quotes are misplaced. Use the form example with your variable instead of a form reference (or skip the variable and just use the form reference).

 

[boblarson]

p = DCount("*", "Points", "PointID =" & Chr(34) & currPt & Chr(34))

or

p = DCount("*", "Points", "PointID ='" & currPt & "'")

 

[brodkat]

hey thanks so much!

both for the code from bob (I'm so pleased it finally works!)

and for the reference from baldy (definitely helpful)