ERROR IN VB CODE PROMPT FOR MACRO

[DIVINEDAR0956]

I have written the following vb code. It prompts me for a macro and I can't figure out why. My STEP INTO doesn't work. Please can someone help.

Private Sub JOB_NUMBER_BeforeUpdate(Cancel As Integer)
Dim TableName As String
Dim Criteria As String
Dim Message As String
Dim NumRecords As Long
Dim rs As Recordset

Set rs = Me.RecordsetClone
TableName = &quot;<Edit_Project>&quot;
Criteria = &quot;[JobNumber]=&quot; & &quot;'&quot; & Me![JobNumber] & &quot;'&quot;
NumRecords = DCount(&quot;JobNumber&quot;, Edit_Project, Criteria)
If NumRecords > 0 Then
Message = &quot;Error! That JobNumber already exists in the table, Cannot Add!&quot;
MsgBox Message, vbCritical
Me.Undo
rs.FindFirst Criteria
Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing
Exit Sub
End If
End Sub

Thank you in advance for your help.

 

[VBslammer]

The DCount() function has Edit_Project as the domain instead of your 'TableName' variable.
VBSlammer

 

[DIVINEDAR0956]

Thank you VBSlammer. Help me if you can. That worked. Now I get an error using Set rs = Me.RecordsetClone saying that invalid use of Me keyword.

Also if you know an easier way of to do what I'm trying to do please let me know.

Along with my Edit Project Table I have 4 other tables with information regarding the Job Number. I want to be able to fill in the 4 other tables along with the Edit Project Table from my storage with the information regarding the Edit Project Table. Once done I would like to ask if the information can be updated or copied with another Job Number replacing that Job Number. I want the information that is pulled from all the tables to just be a copy of what is in my storage. I need a VB that when I open up my Edit Project Form and enter the Job Number all the other tables are populated with the information. If the information does not exist I want to be able to add a new project and send it to storage.

So if you can please help me it would so appreciated.

Thank you very much for your help in advance.

 

[VBslammer]

Try this:

Dim rs As DAO.Recordset

Sometimes you have to remove ambiguity from your declarations.

VBSlammer