syntax error, missing operator

[mhaledot]

I feel really dumb now. I added that line, ran the form (clicked the button after entering some data), but it won't produce anything.

...

    If (PI_Number & vbNullString) = vbNullString Then Exit Sub
    Dim rs1 As DAO.Recordset
    Set rs1 = Me.RecordsetClone
Debug.Print "[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"
    rs1.FindFirst ("[ReportType]= '" & Report_Type & "' AND [PI] = '" & PI_Number & "'")

...

I think all this is really accomplishing is getting me frustrated beyond belief...

[patjones]

It has to produce something. You're looking in the Immediate window, correct? Go View > Immediate and it will appear along the bottom of your VBA screen. Then re-run the code and the result of the Debug.Print should be shown there.

[mhaledot]

Thank you. Previously I got a pop-up with the error...

Anyhow. It produced:
[ReportType]= 'CR' AND [PI] = '0000409'

and
[ReportType]= 'Rev CR + L&D' AND [PI] = '1234567'

and
[ReportType]= 'ICTM' AND [PI] = '1234567'

(I had tried a few times). I'm pretty sure this is what I want it to produce. I cannot tell if this is going through the FindFirst though (as I get the same result no matter what I enter)

For instance:

[ReportType]= 'CR' AND [PI] = '0000409' is an existing report.

Should FindFirst be able to take the previous input, look through the recordset (or clone in this case) and then return something? If so, does whatever it returns get assigned to rs1 then?

If my guess above is correct, no wonder my if statement is always true...

[patjones]

Can you remove the parenthesis from around the FindFirst argument and see what happens? It's a detail that I overlooked, but FindFirst doesn't require ( ) when passing arguments in.

[mhaledot]

Removing the parenthensis does not appear to affect the outcome.


That is, the If statement still answers as though true - even when I feed information which should be false.

[mhaledot]

I know I posted it previously, but what I want to have happen is when a user enters a duplicate basicall "catch" the error (number 2950 apparently) and give the user the option to go to the the record they tried to duplicate or change their attempted entry to something not duplicated for appending to the table. If there is no duplication, then just save the entry in the table.

Am I just going about this the entirely wrong way?

[patjones]

OK, what I'm going to do then is suggest a slightly different course of action. Let's say that your columns are in a table called tblProducts. Instead of using RecordsetClone, you can do this:

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT COUNT(*) AS num_dupes FROM tblProducts WHERE [ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"

Set rs = CurrentDB.OpenRecordset(strSQL)

If rs!num_dupes = 0 Then
   'Code to save record here
Else
   MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", vbOKOnly + vbInformation
End If

...

What this does is establishes a recordset of any records that happen to meet your criteria. If no records meet your criteria, then its record count is zero and you can proceed with the save. Otherwise, it contains one or more records and you need to inform the user.

[NeoPa]

ZeppHead80:
but FindFirst doesn't require ( ) when passing arguments in.

See VBA Argument Lists for this side-issue.

[NeoPa]

Having gone back to the original question, and because you (MHaledot) sound like you might appreciate the tips, I thought I'd post a link to How to Debug SQL String. You may find it helpful going forward.

[mhaledot]

Thank you. It may, in fact, help a lot. I know it probably obvious by now, but I'm not really very familiar with VBA... I guess I'm "learning by accident" as my coworkers prove to need more locks than I ever thought my "simple" record keeper database would need.

[mhaledot]

@zepphead

Thank you very much! I implemented the changes (as shown below) and now the code is functioning as I expect! And it has less "meat" which I always like.

Private Sub Save_Record_Click()

'Establish a RecordSet of any Records which meet the search criteria.
'Record count = 0 allows save

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT COUNT(*) AS num_dupes FROM Report_Status WHERE [ReportType] = '" & Report_Type & "' AND [PI] = '" & PI_Number & "'"

Set rs = CurrentDb.OpenRecordset(strSQL)

If rs!num_dupes = 0 Then
    'If no duplicates, save the record
    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "Record Saved!", vbOKOnly + vbInformation
Else
    'If there are duplicates, give an alert box to inform user
    MsgBox "Sorry, a review for '" & Me.PI_Number & "' " & Me.Report_Type & "' already exists.", vbOKOnly + vbInformation
End If

End Sub

Sorry about the //documentation. That is from my java days in school where the teacher wanted us to document everything. I think it helps here (for me at least).

[patjones]

Excellent. I wish I could say why the FindFirst call didn't work properly, and perhaps NeoPa or someone else can yield some insight in that regard.

I personally find myself using this method quite a bit as opposed to the form's RecordsetClone property. As you work your way through Access and VBA, you'll find that there are numerous methods for populating a recordset, and in fact that there are different types of recordsets (DAO and ADO for instance).

Documentation is a good thing. I know that I appreciate picking up someone else's well-documented code.

Pat

[mhaledot]

Not that it really matters (to the specific topic of this thread), but thanks to both you and NeoPa I have a code that searches for the record and yells at the user if they duplicate it (not literally of course - but that may one day be necessary) and takes them to the existing record if it does exist (that last part I actually figured out on my own - I can be taught!).

Seriously, this will make the database that much more efficient. Thanks!

[patjones]

Just so that you're aware of it, there are lots of ways to do duplicate checking in a database.

In your situation, another possibility is to go in to table design view and actually specify the compound key. In the code, you can do an INSERT of whatever the user is trying to save. If the project ID/report type combination already exists in the table, then Access will raise an error and the save will not succeed. From a coding standpoint this is even leaner than what we've done here.