J
John Spencer
Since you are referring to controls, I think you will find that you can refer
to Client Id as Client_ID as the name of the control.
Access will replace spaces in the names of controls with underscore characters.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
to Client Id as Client_ID as the name of the control.
Access will replace spaces in the names of controls with underscore characters.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
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
:
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