autoincrement PK

is a new record with only the SL_Client_No field updated/incremented (SL0002).
Then i want to copy fields from existing form (Me![First_Name]) about 10
fields on current form and then the value from a combo box on the same form.

"John W. Vinson" wrote:

On Thu, 27 Sep 2007 10:33:02 -0700, Chuck
wrote:

Guys, i have no trouble creating a new record and incrementing HAxxxx.
I'm in a seperate form now and i seach on this value (HA1234) using unbound
combo box and i want to update a table (tblclients, not related to form im
in) and copy HA1234 into a field in tblclients. How do i do this?
is it currentdb.execute .......?????

How do you know which record in tblClients to update? Do you want to update an
existing record, or add a new record? Where is the other data for this record
coming from?

John W. Vinson [MVP]

On Thu, 27 Sep 2007 12:25:03 -0700, Chuck
wrote:

is a new record with only the SL_Client_No field updated/incremented (SL0002).
Then i want to copy fields from existing form (Me![First_Name]) about 10
fields on current form and then the value from a combo box on the same form.

Is this a bound form? Why *from a form* rather than from a table? Data is NOT
stored in forms! Are you perhaps loading a bound (or unbound???) form from one
table and exporting the data to another table?

If you're copying the data from one table to another table, an Append query
executed from code would be preferred. If you indeed must use the form as an
intermediary, there are a few ways to do it, none ideal - an Append query with
ten parameters from the form, or opening a Recordset based on the target table
and populating it in code... both rather difficult to do and even harder to
maintain.

As an Append query, assuming you have data in tblProspects, that you have one
of those records selected on the form, and you want to copy all of the fields
except the SL_Client_No, something like this should work:

Private Sub cmd_CreateClient_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strNextID As String
On Error GoTo Proc_Err
strNextID = "SL" & Format(Val(Right, DMax("SL_Client_No", [Clients]), 4) _
+ 1, "0000")
Set db = CurrentDb
strSQL = "INSERT INTO Clients ([SL_Client_No], [thisfield], [thatfield])" _
" SELECT '" & strNextID & "' AS SL_Client_No, [thisfield], [thatfield]" _
" FROM Prospects WHERE DL_Prospect_No = '" _
& Forms!yourformname!DL_Prospect_No & "';"
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Number & " in cmd_CreateClient_Click:" & vbCrLf _
& Err.Description
Resume Proc_Exit
End Sub

John W. Vinson [MVP]

On Sep 27, 5:35 pm, John W. Vinson
wrote:
I believe that the JET query engine processes frustrated outer joins more
efficiently than NOT EXISTS clauses

I often get frustrated with JET's outer joins hence why I use NOT
EXISTS clauses ;-)

Jamie.

--

and the bonus is for the most part faster query execution
and you can "upsize" the query to MSSQL & Oracle much more easily
and it tends to be more readable (IMnHO)

Pieter

"Jamie Collins" wrote in message
ups.com...
On Sep 27, 5:35 pm, John W. Vinson
wrote:
I believe that the JET query engine processes frustrated outer joins more
efficiently than NOT EXISTS clauses

I often get frustrated with JET's outer joins hence why I use NOT
EXISTS clauses ;-)

Jamie.

--

Hey John, I must be doing something really wrong with my design as didn't
think it would take 25 posts to get it right. As you mentioned, i think i may
need to abandon the HA0000 and SL000 PK idea. *bare with me one last time
please*

Since my last post i took your advise and created a subform in my
frmNewAddress to link the address to the client and that works fine.
(one-to-many relations with RI turned on) I also added code to automatically
increment the SL0000 (in the subform) which updates SL_CLient_No field in
tblclients. So far so good.

Now the tricky part - there are other tables [tblemployment, tblinvestment
etc) that link to tblclient using the PK "SL_Client_No" and i need to
propogate the SL0000 field to these other tables and create the record. The
tables are linked with RI turned on and cascade (something or other) turned
on. tblemployment does not have an autonumber field. why is tblemployment not
creating a record with PK of SL0000?

Sorry to be a pest!!


"John W. Vinson" wrote:

On Thu, 27 Sep 2007 12:25:03 -0700, Chuck
wrote:

is a new record with only the SL_Client_No field updated/incremented (SL0002).
Then i want to copy fields from existing form (Me![First_Name]) about 10
fields on current form and then the value from a combo box on the same form.

Is this a bound form? Why *from a form* rather than from a table? Data is NOT
stored in forms! Are you perhaps loading a bound (or unbound???) form from one
table and exporting the data to another table?

If you're copying the data from one table to another table, an Append query
executed from code would be preferred. If you indeed must use the form as an
intermediary, there are a few ways to do it, none ideal - an Append query with
ten parameters from the form, or opening a Recordset based on the target table
and populating it in code... both rather difficult to do and even harder to
maintain.

As an Append query, assuming you have data in tblProspects, that you have one
of those records selected on the form, and you want to copy all of the fields
except the SL_Client_No, something like this should work:

Private Sub cmd_CreateClient_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strNextID As String
On Error GoTo Proc_Err
strNextID = "SL" & Format(Val(Right, DMax("SL_Client_No", [Clients]), 4) _
+ 1, "0000")
Set db = CurrentDb
strSQL = "INSERT INTO Clients ([SL_Client_No], [thisfield], [thatfield])" _
" SELECT '" & strNextID & "' AS SL_Client_No, [thisfield], [thatfield]" _
" FROM Prospects WHERE DL_Prospect_No = '" _
& Forms!yourformname!DL_Prospect_No & "';"
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Err:
MsgBox "Error " & Err.Number & " in cmd_CreateClient_Click:" & vbCrLf _
& Err.Description
Resume Proc_Exit
End Sub

John W. Vinson [MVP]

On Sep 28, 11:17 am, "Pieter Wijnen"
it.isi.llegal.to.send.unsollicited.mail.wijnen.no
wrote:
and the bonus is for the most part faster query execution
and you can "upsize" the query to MSSQL & Oracle much more easily

I don't get your argument about 'upsizing' because NOT EXISTS is
supported in both SQL Server and Oracle. Do you mean it foils Access's
Upsizing Wizard Thing? So do CHECK constraints but I wouldn't drop
table integrity constraints as a result.

Have you ever come across the 'Join expression not supported' problem
with OUTER JOIN in Jet? See http://tinyurl.com/yunof4.

and it tends to be more readable (IMnHO)

Humble or no, that's in the eye of the beholder

Jamie.

--

On Fri, 28 Sep 2007 07:11:02 -0700, Chuck
wrote:

Now the tricky part - there are other tables [tblemployment, tblinvestment
etc) that link to tblclient using the PK "SL_Client_No" and i need to
propogate the SL0000 field to these other tables and create the record. The
tables are linked with RI turned on and cascade (something or other) turned
on. tblemployment does not have an autonumber field. why is tblemployment not
creating a record with PK of SL0000?

Cascade Updates UPDATES the related table when you *change the value of* an
existing primary key.

It does not - and cannot, and should not - automagically create a new record.
That's not its function.

If you want to automagically create a new record in tblEmployment, and have it
inherit the clientID - *use a Subform*. That's the appropriate tool. Use the
ClientID as the master and child link field of the subform; base the mainform
on the Clients table and the subform on tblEmployment. The ID field need not
be calculated, need not be entered, needn't even be displayed on either form
(hence a meaningless autonumber will work perfectly well as a linker).

It is *NOT* necessary or appropriate to create an empty "placeholder" record
in tblEmployement or tblInvestment at the time that a record is created in the
Clients table. Instead, if you use a Subform, the linking field will be added
at the moment that you start entering data into the tblEmployment record...
not before.

You've simply gotten off on the wrong track by trying to program these keys by
yourself, rather than letting Access do automatically what it does very well!

John W. Vinson [MVP]

no, I just meant to say you can port the SQL without (significant) changes

Pieter

"Jamie Collins" wrote in message
oups.com...
On Sep 28, 11:17 am, "Pieter Wijnen"
it.isi.llegal.to.send.unsollicited.mail.wijnen.no
wrote:
and the bonus is for the most part faster query execution
and you can "upsize" the query to MSSQL & Oracle much more easily

I don't get your argument about 'upsizing' because NOT EXISTS is
supported in both SQL Server and Oracle. Do you mean it foils Access's
Upsizing Wizard Thing? So do CHECK constraints but I wouldn't drop
table integrity constraints as a result.

Have you ever come across the 'Join expression not supported' problem
with OUTER JOIN in Jet? See http://tinyurl.com/yunof4.

and it tends to be more readable (IMnHO)

Humble or no, that's in the eye of the beholder

Jamie.

--