autoincrement PK

  • Thread starter Thread starter Guest
  • Start date Start date
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 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.
Click to expand...

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]
 
I believe that the JET query engine processes frustrated outer joins more
efficiently than NOT EXISTS clauses
Click to expand...

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

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]
Click to expand...
 
and the bonus is for the most part faster query execution
and you can "upsize" the query to MSSQL & Oracle much more easily
Click to expand...

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)
Click to expand...

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

Jamie.

--
 
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?
Click to expand...

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]
 
Back
Top