creating a new record with a unique ID #

  • Thread starter Thread starter Emma
  • Start date Start date
E

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
 
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
 
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?


Klatuu said:
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


Emma said:
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
Click to expand...
Click to expand...
 
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


Emma said:
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?


Klatuu said:
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


Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
When I press the button to run the code in form view that message comes up

Klatuu said:
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


Emma said:
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?


Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Klatuu said:
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


Emma said:
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?


Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Emma said:
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


Klatuu said:
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


Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Emma said:
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.

Emma said:
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
Click to expand...
Click to expand...
 
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"

Klatuu said:
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


Emma said:
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.

Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 & ";"
Click to expand...

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


Emma said:
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"

Klatuu said:
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


Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
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!!!

Klatuu said:
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 & ";"
Click to expand...

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


Emma said:
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"

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
I don't understand the question, Emma.
Can you be more specific and detailed, please.
--
Dave Hargis, Microsoft Access MVP


Emma said:
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!!!

Klatuu said:
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 & ";"
Click to expand...

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


Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

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


Emma said:
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!!!

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Emma said:
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

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


Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.


Klatuu said:
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


Emma said:
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

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Emma said:
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.


Klatuu said:
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


Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks so much,

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

Klatuu said:
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


Emma said:
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.


Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
Emma said:
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


Klatuu said:
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


Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
Hi Klatuu,

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



Emma said:
Thanks so much,

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

Klatuu said:
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


Emma said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Emma said:
Hi Klatuu,

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



Emma said:
Thanks so much,

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

Klatuu said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top