If a record doesn't exist

E

Emma

I am trying to set up the next record on the button if it is blank to get the
highest number and put it in the Client ID field. Here's my code which isn't
working:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim intNewID As Integer
Dim strSQL As String
Dim strSQL2 As String

On Error GoTo Err_Command116_Click

'See if Client ID exists if not get new one

If ![Tbl Client Information].[Client ID] Then

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[Client ID]", "[Tbl Client Information]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Tbl Client Information] ([Client ID]) SELECT " &
intNewID & ";"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO [Tbl Client Information] ([Status]) SELECT
Active;"

'CurrentDb.Execute strSQL2, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[Client ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.Status.Value = "Active"
Me.Combo157.Value = "Canadian"
Me.Children.Value = "0"
Me.Other.Value = "0"
Me.[Family Profile].Value = "0"
Me.[Marital Status].Value = "0"
Me![Intake Coordinator].Value = CurrentUser
End If


DoCmd.GoToRecord , , acNext

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub
 
J

JString

Is there any reason why you don't want to use an autonumber to handle Client
ID?
 
E

Emma

I'm already using the autonumber for something else

JString said:
Is there any reason why you don't want to use an autonumber to handle Client
ID?

Emma said:
I am trying to set up the next record on the button if it is blank to get the
highest number and put it in the Client ID field. Here's my code which isn't
working:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim intNewID As Integer
Dim strSQL As String
Dim strSQL2 As String

On Error GoTo Err_Command116_Click

'See if Client ID exists if not get new one

If ![Tbl Client Information].[Client ID] Then

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[Client ID]", "[Tbl Client Information]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Tbl Client Information] ([Client ID]) SELECT " &
intNewID & ";"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO [Tbl Client Information] ([Status]) SELECT
Active;"

'CurrentDb.Execute strSQL2, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[Client ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.Status.Value = "Active"
Me.Combo157.Value = "Canadian"
Me.Children.Value = "0"
Me.Other.Value = "0"
Me.[Family Profile].Value = "0"
Me.[Marital Status].Value = "0"
Me![Intake Coordinator].Value = CurrentUser
End If


DoCmd.GoToRecord , , acNext

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub
Click to expand...
Click to expand...
 
J

JString

You can have more than one autonumber :]

Emma said:
I'm already using the autonumber for something else

JString said:
Is there any reason why you don't want to use an autonumber to handle Client
ID?

Emma said:
I am trying to set up the next record on the button if it is blank to get the
highest number and put it in the Client ID field. Here's my code which isn't
working:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim intNewID As Integer
Dim strSQL As String
Dim strSQL2 As String

On Error GoTo Err_Command116_Click

'See if Client ID exists if not get new one

If ![Tbl Client Information].[Client ID] Then

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[Client ID]", "[Tbl Client Information]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Tbl Client Information] ([Client ID]) SELECT " &
intNewID & ";"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO [Tbl Client Information] ([Status]) SELECT
Active;"

'CurrentDb.Execute strSQL2, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[Client ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.Status.Value = "Active"
Me.Combo157.Value = "Canadian"
Me.Children.Value = "0"
Me.Other.Value = "0"
Me.[Family Profile].Value = "0"
Me.[Marital Status].Value = "0"
Me![Intake Coordinator].Value = CurrentUser
End If


DoCmd.GoToRecord , , acNext

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

JString

Your problem *might* be this line of code:

If ![Tbl Client Information].[Client ID] Then

Is the form bound to 'Tbl Client Information' ? If so try this:

If isnull(Me![ClientID]) Then
 
E

Emma

I'm having a problem checking if the ClientID Exists in this line If ![Tbl
Client Information].[Client ID] Then
also I'm getting can't find the field '|' referred to in your expression.

I am using an autonumber but for some reason it keeps coming up with a blank
which turns into the number 0. So the number 0 already exists so I can't have
a duplicate and I'm getting an error there that's why I'm trying to do it
this way.

JString said:
I haven't picked up on any flaws yet... what sort of errors are you getting?

Emma said:
I am trying to set up the next record on the button if it is blank to get the
highest number and put it in the Client ID field. Here's my code which isn't
working:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim intNewID As Integer
Dim strSQL As String
Dim strSQL2 As String

On Error GoTo Err_Command116_Click

'See if Client ID exists if not get new one

If ![Tbl Client Information].[Client ID] Then

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[Client ID]", "[Tbl Client Information]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Tbl Client Information] ([Client ID]) SELECT " &
intNewID & ";"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO [Tbl Client Information] ([Status]) SELECT
Active;"

'CurrentDb.Execute strSQL2, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[Client ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.Status.Value = "Active"
Me.Combo157.Value = "Canadian"
Me.Children.Value = "0"
Me.Other.Value = "0"
Me.[Family Profile].Value = "0"
Me.[Marital Status].Value = "0"
Me![Intake Coordinator].Value = CurrentUser
End If


DoCmd.GoToRecord , , acNext

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub
Click to expand...
Click to expand...
 
J

JString

It sort of looks like you're trying to use a C-style 'not' logical operator
there. If so, VB uses "Not" instead. This also won't work unless your
ClientID fields have a default value of zero, otherwise they will default to
Null and your test will fail every time, probably with an "Invalid use of
Null" error.

Also, you should probably check if the ClientID exists for the current
record or for one specific record. You can use DLookup to do this pretty
easily. Its syntax is simliar to the DMax function that you used elsewhere.

Another thing is


JString said:
Your problem *might* be this line of code:

If ![Tbl Client Information].[Client ID] Then

Is the form bound to 'Tbl Client Information' ? If so try this:

If isnull(Me![ClientID]) Then

Emma said:
I am trying to set up the next record on the button if it is blank to get the
highest number and put it in the Client ID field. Here's my code which isn't
working:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim intNewID As Integer
Dim strSQL As String
Dim strSQL2 As String

On Error GoTo Err_Command116_Click

'See if Client ID exists if not get new one

If ![Tbl Client Information].[Client ID] Then

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[Client ID]", "[Tbl Client Information]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Tbl Client Information] ([Client ID]) SELECT " &
intNewID & ";"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO [Tbl Client Information] ([Status]) SELECT
Active;"

'CurrentDb.Execute strSQL2, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[Client ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.Status.Value = "Active"
Me.Combo157.Value = "Canadian"
Me.Children.Value = "0"
Me.Other.Value = "0"
Me.[Family Profile].Value = "0"
Me.[Marital Status].Value = "0"
Me![Intake Coordinator].Value = CurrentUser
End If


DoCmd.GoToRecord , , acNext

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub
Click to expand...
Click to expand...
 
J

John W. Vinson

I'm having a problem checking if the ClientID Exists in this line If ![Tbl
Client Information].[Client ID] Then
also I'm getting can't find the field '|' referred to in your expression.
Click to expand...

Change it to

Me![Client ID]
 
J

John Spencer (MVP)

Just a reply to this statement. NO, you cannot have more than one autonumber
in a table. At least with Access' native JET engine and I suspect with most
(if not all) other database engines.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
E

Emma

Phew, it's working I'm so happy! Thanks! Cheers!

JString said:
It sort of looks like you're trying to use a C-style 'not' logical operator
there. If so, VB uses "Not" instead. This also won't work unless your
ClientID fields have a default value of zero, otherwise they will default to
Null and your test will fail every time, probably with an "Invalid use of
Null" error.

Also, you should probably check if the ClientID exists for the current
record or for one specific record. You can use DLookup to do this pretty
easily. Its syntax is simliar to the DMax function that you used elsewhere.

Another thing is


JString said:
Your problem *might* be this line of code:

If ![Tbl Client Information].[Client ID] Then

Is the form bound to 'Tbl Client Information' ? If so try this:

If isnull(Me![ClientID]) Then

Emma said:
I am trying to set up the next record on the button if it is blank to get the
highest number and put it in the Client ID field. Here's my code which isn't
working:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim intNewID As Integer
Dim strSQL As String
Dim strSQL2 As String

On Error GoTo Err_Command116_Click

'See if Client ID exists if not get new one

If ![Tbl Client Information].[Client ID] Then

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[Client ID]", "[Tbl Client Information]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Tbl Client Information] ([Client ID]) SELECT " &
intNewID & ";"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO [Tbl Client Information] ([Status]) SELECT
Active;"

'CurrentDb.Execute strSQL2, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[Client ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.Status.Value = "Active"
Me.Combo157.Value = "Canadian"
Me.Children.Value = "0"
Me.Other.Value = "0"
Me.[Family Profile].Value = "0"
Me.[Marital Status].Value = "0"
Me![Intake Coordinator].Value = CurrentUser
End If


DoCmd.GoToRecord , , acNext

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

JString

That is true, but you can join tables using queries, and in that sense you
can use more than one autonumber. If your tables are properly normalized,
you should never even need to have more than one autonumber per table.

John Spencer (MVP) said:
Just a reply to this statement. NO, you cannot have more than one autonumber
in a table. At least with Access' native JET engine and I suspect with most
(if not all) other database engines.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
You can have more than one autonumber :]

:
Click to expand...
Click to expand...
 
J

JString

You're very welcome Emma. Good luck with the rest of your project!

Emma said:
Phew, it's working I'm so happy! Thanks! Cheers!

JString said:
It sort of looks like you're trying to use a C-style 'not' logical operator
there. If so, VB uses "Not" instead. This also won't work unless your
ClientID fields have a default value of zero, otherwise they will default to
Null and your test will fail every time, probably with an "Invalid use of
Null" error.

Also, you should probably check if the ClientID exists for the current
record or for one specific record. You can use DLookup to do this pretty
easily. Its syntax is simliar to the DMax function that you used elsewhere.

Another thing is


JString said:
Your problem *might* be this line of code:

If ![Tbl Client Information].[Client ID] Then

Is the form bound to 'Tbl Client Information' ? If so try this:

If isnull(Me![ClientID]) Then

:

I am trying to set up the next record on the button if it is blank to get the
highest number and put it in the Client ID field. Here's my code which isn't
working:

Private Sub Command116_Click()

On Error GoTo Err_Command116_Click

Dim intNewID As Integer
Dim strSQL As String
Dim strSQL2 As String

On Error GoTo Err_Command116_Click

'See if Client ID exists if not get new one

If ![Tbl Client Information].[Client ID] Then

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[Client ID]", "[Tbl Client Information]"), 0) + 1

'Write it to the table ASAP to avoid duplicates in a multi user environment

strSQL = "INSERT INTO [Tbl Client Information] ([Client ID]) SELECT " &
intNewID & ";"

CurrentDb.Execute strSQL, dbFailOnError

'strSQL2 = "INSERT INTO [Tbl Client Information] ([Status]) SELECT
Active;"

'CurrentDb.Execute strSQL2, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[Client ID] = " & intNewID & ""
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.Status.Value = "Active"
Me.Combo157.Value = "Canadian"
Me.Children.Value = "0"
Me.Other.Value = "0"
Me.[Family Profile].Value = "0"
Me.[Marital Status].Value = "0"
Me![Intake Coordinator].Value = CurrentUser
End If


DoCmd.GoToRecord , , acNext

Exit_Command116_Click:
Exit Sub

Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

New Record 8
Help with code 3
Making form go blank when new Record is called 1
VBA works in orignal but fails in copy of database 1
creating a new record with a unique ID # 20
Multiple Step OLE DB operation generated errors 8
delete sub and stay on same parent record 4
MakeSchedule function 6

Top