creating a new record with a unique ID #

[Emma]

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Klatuu]

Private Sub newrcd_Click()
Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ClientID]","[tbl Client Information]"), 0) + 1

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

strSQL = "INSERT INTO [tbl Client Informatin] (CliendID) SELECT " & _
intNewID & " AS Dummy;"
Currentdb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ClientID] = " & intNewID
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

 

[Emma]

Thanks Klatuu, unfortunately the error message I'm getting is path related
now. It says Could not find file "\\SERVER\Users\Emma\My Documents\Tlb Client
Information.mdb" Any idea how I can change the path to the correct location,
do I have to hard code this?

Private Sub newrcd_Click()
Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ClientID]","[tbl Client Information]"), 0) + 1

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

strSQL = "INSERT INTO [tbl Client Informatin] (CliendID) SELECT " & _
intNewID & " AS Dummy;"
Currentdb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ClientID] = " & intNewID
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

--
Dave Hargis, Microsoft Access MVP

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Klatuu]

Where is it getting that error?
There is nothing in the code I posted that addresses a path at all.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, unfortunately the error message I'm getting is path related
now. It says Could not find file "\\SERVER\Users\Emma\My Documents\Tlb Client
Information.mdb" Any idea how I can change the path to the correct location,
do I have to hard code this?

Private Sub newrcd_Click()
Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ClientID]","[tbl Client Information]"), 0) + 1

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

strSQL = "INSERT INTO [tbl Client Informatin] (CliendID) SELECT " & _
intNewID & " AS Dummy;"
Currentdb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ClientID] = " & intNewID
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

--
Dave Hargis, Microsoft Access MVP

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

When I press the button to run the code in form view that message comes up

Where is it getting that error?
There is nothing in the code I posted that addresses a path at all.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, unfortunately the error message I'm getting is path related
now. It says Could not find file "\\SERVER\Users\Emma\My Documents\Tlb Client
Information.mdb" Any idea how I can change the path to the correct location,
do I have to hard code this?

Private Sub newrcd_Click()
Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ClientID]","[tbl Client Information]"), 0) + 1

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

strSQL = "INSERT INTO [tbl Client Informatin] (CliendID) SELECT " & _
intNewID & " AS Dummy;"
Currentdb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ClientID] = " & intNewID
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

--
Dave Hargis, Microsoft Access MVP


:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

I was getting a syntax error on the INSERT INTO line so I changed it to
strSQL = "INSERT INTO [tbl Client Information].[Client ID] SELECT " & _
intNewID & " AS Dummy;"

And that's when the path error came up

Hope this helps, Emma

Where is it getting that error?
There is nothing in the code I posted that addresses a path at all.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, unfortunately the error message I'm getting is path related
now. It says Could not find file "\\SERVER\Users\Emma\My Documents\Tlb Client
Information.mdb" Any idea how I can change the path to the correct location,
do I have to hard code this?

Private Sub newrcd_Click()
Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ClientID]","[tbl Client Information]"), 0) + 1

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

strSQL = "INSERT INTO [tbl Client Informatin] (CliendID) SELECT " & _
intNewID & " AS Dummy;"
Currentdb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ClientID] = " & intNewID
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

--
Dave Hargis, Microsoft Access MVP


:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Klatuu]

Your syntax is incorrect and causing the problem. It may have been the typo
where I misspelled the name of the ClientID field and the name of the table:

strSQL = "INSERT INTO [tbl Client Informatin] (CliendID) SELECT " & _
intNewID & " AS Dummy;"

probably should be:
strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

--
Dave Hargis, Microsoft Access MVP

I was getting a syntax error on the INSERT INTO line so I changed it to
strSQL = "INSERT INTO [tbl Client Information].[Client ID] SELECT " & _
intNewID & " AS Dummy;"

And that's when the path error came up

Hope this helps, Emma

Where is it getting that error?
There is nothing in the code I posted that addresses a path at all.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, unfortunately the error message I'm getting is path related
now. It says Could not find file "\\SERVER\Users\Emma\My Documents\Tlb Client
Information.mdb" Any idea how I can change the path to the correct location,
do I have to hard code this?


:

Private Sub newrcd_Click()
Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ClientID]","[tbl Client Information]"), 0) + 1

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

strSQL = "INSERT INTO [tbl Client Informatin] (CliendID) SELECT " & _
intNewID & " AS Dummy;"
Currentdb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ClientID] = " & intNewID
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

--
Dave Hargis, Microsoft Access MVP


:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Klatuu]

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Klatuu]

Client ID needs to be in brackets because there is a space in the name.

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

intNewID & ";"

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

Client ID needs to be in brackets because there is a space in the name.

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

intNewID & ";"

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Klatuu]

I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

Client ID needs to be in brackets because there is a space in the name.

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

intNewID & ";"

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

:

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

I would like to do the same thing with another table, this time I'd like to
create a new file when the button is pressed that has the client's first and
last names and client ID number already when the new record is created. Does
this make sense?

Thanks again, Emma

I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

Client ID needs to be in brackets because there is a space in the name.

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

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

:

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Klatuu]

Well, yes and no.
It makes sense in that I understand your question, but it does not make
sense to carry the Client's name in more than one place. That violates
database normalization rules.

It would make sense to store the ClientID in another table if that table is
data related to the client.

Also, you said "create a new file"
Do you mean another mdb, another table, or another record?
What is it and what will it be used for?
--
Dave Hargis, Microsoft Access MVP

I would like to do the same thing with another table, this time I'd like to
create a new file when the button is pressed that has the client's first and
last names and client ID number already when the new record is created. Does
this make sense?

Thanks again, Emma

I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

:

Client ID needs to be in brackets because there is a space in the name.

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

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

:

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

Hi Klatuu,

I have a form which brings up one clients information for several records.
So I can click next and go the the next record which for that client only.
However I would like to create a new record which already has the client's
name and ID so the user doesn't have to enter this information. Right now
when the user clicks new record a blank new record comes up. That's why I
would like to keep the client ID and Name, I guess your right I could just
pull them from the table, but I'm not sure how to do this and I thought using
code would be easier.

Well, yes and no.
It makes sense in that I understand your question, but it does not make
sense to carry the Client's name in more than one place. That violates
database normalization rules.

It would make sense to store the ClientID in another table if that table is
data related to the client.

Also, you said "create a new file"
Do you mean another mdb, another table, or another record?
What is it and what will it be used for?
--
Dave Hargis, Microsoft Access MVP

I would like to do the same thing with another table, this time I'd like to
create a new file when the button is pressed that has the client's first and
last names and client ID number already when the new record is created. Does
this make sense?

Thanks again, Emma

I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

:

Client ID needs to be in brackets because there is a space in the name.

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

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

:

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Klatuu]

I see what you are wanting. Actually it would be easier and better to use
the button you have now to go to a new record. But, you don't need to create
the record quite the same way. A better way would be to use the Default
Value properties of the controls where you have the client ID and name and
set them in the click event of the button.

It would be something like this:

With Me
.ClientID.DefaultValue = .ClientID
.ClientName.DefaultValue = .ClientName
End With
Docmd.GotoRecord acNewRec
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu,

I have a form which brings up one clients information for several records.
So I can click next and go the the next record which for that client only.
However I would like to create a new record which already has the client's
name and ID so the user doesn't have to enter this information. Right now
when the user clicks new record a blank new record comes up. That's why I
would like to keep the client ID and Name, I guess your right I could just
pull them from the table, but I'm not sure how to do this and I thought using
code would be easier.

Well, yes and no.
It makes sense in that I understand your question, but it does not make
sense to carry the Client's name in more than one place. That violates
database normalization rules.

It would make sense to store the ClientID in another table if that table is
data related to the client.

Also, you said "create a new file"
Do you mean another mdb, another table, or another record?
What is it and what will it be used for?
--
Dave Hargis, Microsoft Access MVP

I would like to do the same thing with another table, this time I'd like to
create a new file when the button is pressed that has the client's first and
last names and client ID number already when the new record is created. Does
this make sense?

Thanks again, Emma

:

I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

:

Client ID needs to be in brackets because there is a space in the name.

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

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

:

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

Thanks so much,

I'll give it a try on Monday. Have a great weekend

I see what you are wanting. Actually it would be easier and better to use
the button you have now to go to a new record. But, you don't need to create
the record quite the same way. A better way would be to use the Default
Value properties of the controls where you have the client ID and name and
set them in the click event of the button.

It would be something like this:

With Me
.ClientID.DefaultValue = .ClientID
.ClientName.DefaultValue = .ClientName
End With
Docmd.GotoRecord acNewRec
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu,

I have a form which brings up one clients information for several records.
So I can click next and go the the next record which for that client only.
However I would like to create a new record which already has the client's
name and ID so the user doesn't have to enter this information. Right now
when the user clicks new record a blank new record comes up. That's why I
would like to keep the client ID and Name, I guess your right I could just
pull them from the table, but I'm not sure how to do this and I thought using
code would be easier.

Well, yes and no.
It makes sense in that I understand your question, but it does not make
sense to carry the Client's name in more than one place. That violates
database normalization rules.

It would make sense to store the ClientID in another table if that table is
data related to the client.

Also, you said "create a new file"
Do you mean another mdb, another table, or another record?
What is it and what will it be used for?
--
Dave Hargis, Microsoft Access MVP


:

I would like to do the same thing with another table, this time I'd like to
create a new file when the button is pressed that has the client's first and
last names and client ID number already when the new record is created. Does
this make sense?

Thanks again, Emma

:

I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

:

Client ID needs to be in brackets because there is a space in the name.

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

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

:

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[atze3]

I was getting a syntax error on the INSERT INTO line so I changed it to
strSQL = "INSERT INTO [tbl Client Information].[Client ID] SELECT " & _
intNewID & " AS Dummy;"

And that's when the path error came up

Hope this helps, Emma

Where is it getting that error?
There is nothing in the code I posted that addresses a path at all.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, unfortunately the error message I'm getting is path
related
now. It says Could not find file "\\SERVER\Users\Emma\My Documents\Tlb
Client
Information.mdb" Any idea how I can change the path to the correct
location,
do I have to hard code this?


:

Private Sub newrcd_Click()
Dim intNewID As Integer
Dim strSQL As String

On Error GoTo Err_newrcd_Click

'Find the current high number and Add 1 to it

intNewID = Nz(DMax("[ClientID]","[tbl Client Information]"), 0) +
1

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

strSQL = "INSERT INTO [tbl Client Informatin] (CliendID) SELECT "
& _
intNewID & " AS Dummy;"
Currentdb.Execute strSQL, dbFailOnError

'Make the new record the current record

Me.Requery
With Me.RecordsetClone
.FindFirst "[ClientID] = " & intNewID
If Not .NoMatch Then
Me.BookMark = .Bookmark
End If
End With

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

--
Dave Hargis, Microsoft Access MVP


:

Hello I have a button that clears the form so the user can enter a
new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual
Basic
confused together, I would like some help coming up with code that
works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

Hi Klatuu,

How do I code the Client ID, First Name and Last Name? As I have spaces in
the names in the Database.

Thanks so much,

I'll give it a try on Monday. Have a great weekend

I see what you are wanting. Actually it would be easier and better to use
the button you have now to go to a new record. But, you don't need to create
the record quite the same way. A better way would be to use the Default
Value properties of the controls where you have the client ID and name and
set them in the click event of the button.

It would be something like this:

With Me
.ClientID.DefaultValue = .ClientID
.ClientName.DefaultValue = .ClientName
End With
Docmd.GotoRecord acNewRec
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu,

I have a form which brings up one clients information for several records.
So I can click next and go the the next record which for that client only.
However I would like to create a new record which already has the client's
name and ID so the user doesn't have to enter this information. Right now
when the user clicks new record a blank new record comes up. That's why I
would like to keep the client ID and Name, I guess your right I could just
pull them from the table, but I'm not sure how to do this and I thought using
code would be easier.


:

Well, yes and no.
It makes sense in that I understand your question, but it does not make
sense to carry the Client's name in more than one place. That violates
database normalization rules.

It would make sense to store the ClientID in another table if that table is
data related to the client.

Also, you said "create a new file"
Do you mean another mdb, another table, or another record?
What is it and what will it be used for?
--
Dave Hargis, Microsoft Access MVP


:

I would like to do the same thing with another table, this time I'd like to
create a new file when the button is pressed that has the client's first and
last names and client ID number already when the new record is created. Does
this make sense?

Thanks again, Emma

:

I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

:

Client ID needs to be in brackets because there is a space in the name.

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

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

:

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma

 

[Emma]

Hi Klatuu,

I've changed the code to look like this:

Private Sub newrcd_Click()

With Me
.[Client ID].DefaultValue = .[Client ID]
.[First Name].DefaultValue = .[First Name]
.[Last Name].DefaultValue = .[Last Name]

End With
DoCmd.GoToRecord , , acNewRec


End Sub


However I'm getting an error when I click the new record button it says
#Name? in the First Name field and the same thing #Name? in the Last Name
field

Hi Klatuu,

How do I code the Client ID, First Name and Last Name? As I have spaces in
the names in the Database.

Thanks so much,

I'll give it a try on Monday. Have a great weekend

I see what you are wanting. Actually it would be easier and better to use
the button you have now to go to a new record. But, you don't need to create
the record quite the same way. A better way would be to use the Default
Value properties of the controls where you have the client ID and name and
set them in the click event of the button.

It would be something like this:

With Me
.ClientID.DefaultValue = .ClientID
.ClientName.DefaultValue = .ClientName
End With
Docmd.GotoRecord acNewRec
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

I have a form which brings up one clients information for several records.
So I can click next and go the the next record which for that client only.
However I would like to create a new record which already has the client's
name and ID so the user doesn't have to enter this information. Right now
when the user clicks new record a blank new record comes up. That's why I
would like to keep the client ID and Name, I guess your right I could just
pull them from the table, but I'm not sure how to do this and I thought using
code would be easier.


:

Well, yes and no.
It makes sense in that I understand your question, but it does not make
sense to carry the Client's name in more than one place. That violates
database normalization rules.

It would make sense to store the ClientID in another table if that table is
data related to the client.

Also, you said "create a new file"
Do you mean another mdb, another table, or another record?
What is it and what will it be used for?
--
Dave Hargis, Microsoft Access MVP


:

I would like to do the same thing with another table, this time I'd like to
create a new file when the button is pressed that has the client's first and
last names and client ID number already when the new record is created. Does
this make sense?

Thanks again, Emma

:

I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu

It's working great!

If I just want the client ID or client name etc... how do I change this line
of code to get the current id?

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

Thanks you are a great help!!!

:

Client ID needs to be in brackets because there is a space in the name.

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

Are the table name and field name correct?
Are you still getting a syntax error?
Is it happening on this line or on the Execute Line?

The more you can tell me, the better I can help you.
--
Dave Hargis, Microsoft Access MVP


:

Hi Klatuu,

Here's the code I'm using

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

CurrentDb.Execute strSQL, dbFailOnError

It seems the same to me. This table is from an old database that I'm
editting. There is also a query called "Qry Active Lookup including closed"

:

I was also wondering if CurrentDB could be used instead of the Database name?
Where are you using a database name?
Let's break this statement down so we undertand what is happening. The code
is a direct copy from one of my apps that works, I only changed the names to
match you original code.


strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " & _
intNewID & " AS Dummy;"

[tbl Client Information] should be the name of the table you want to add the
record to.
(ClientID) should be the name of the field you want to put the new ID number
in.

intNewID is the value you are assigning to the ClientID field and it should
be Dimmed as the same data type as ClientID.

As written, the code contains a line continuation character the _
That means, this logical line continues on the next physical line. Without
it, it would be written as:

strSQL = "INSERT INTO [tbl Client Information] (ClientID) SELECT " &
intNewID & " AS Dummy;"


This line runt the query to add the record to the table.

Currentdb.Execute strSQL, dbFailOnError

--
Dave Hargis, Microsoft Access MVP


:

I was also wondering if CurrentDB could be used instead of the Database name?
I'm not familiar with Visual Basic maybe you can show me how to use this
command if possible.

:

Hello I have a button that clears the form so the user can enter a new
client's information. However I would like to have the ID come up
automatically to the next unique id in the list.

Here's some code I came up with however I have the SQL and Visual Basic
confused together, I would like some help coming up with code that works.

Private Sub newrcd_Click()
On Error GoTo Err_newrcd_Click


Dim TempID As Integer

DoCmd.GoToRecord , , acLast
'TempID = [Tbl Client Information].[ClientID]
'TempID = TempID + 1

DoCmd.GoToRecord , , acNewRec
'[Tbl Client Information].[ClientID] = TempID

Exit_newrcd_Click:
Exit Sub

Err_newrcd_Click:
MsgBox Err.Description
Resume Exit_newrcd_Click

End Sub

Thanks Emma