Table update

C

Chuck

Hi, i have a form frmProspect and 3 tables tblProspect, tblHome and
tblClients. tblHome and tblClients are linked (one-to-many). tblProspect is
not linked to anything.

In frmprospect i have a button (Go) whith a checkbox (Check275).
If the checkbox is selected i want to perform the following:
1. Create new record in tblClient
2. Update a field called SL_Client_No sequentially starting at SL0001
3. Copy about 10 Bound fields from tblPropsects to primarily tblClients (and
other tables as well). Field names are the same.
4. Then using a seperate combox box (HomeAddressSearch), select an address
(qry already done) and Copy the value of HomeAddressSearch to field
Home_Addr_PK in tblClients so i can link clients and Home address.

.....Here is my attempt..


Private Sub Go_Click()
Dim strAddr As String
Dim clientnum As String
Dim intAddr As Integer
On Error GoTo Err_Go_Click
' Ensure checkbox is selected before copy
If Me.Check275 = True Then
DoCmd.GoToRecord , , acNewRec
'Generate new Client #
strAddr = Nz(DMax("[SL_Client_No]", "tblClients"), "SL0000")
intAddr = Val(Mid(strAddr, 3)) 'extract numeric portion
clientnum = Left(strAddr, 2) & Format(intAddr + 1, "0000")

..........need help here

MsgBox "Client Data Copied", vbOKOnly
Else
MsgBox "Box not checked", vbOKOnly
End If

Exit_Go_Click:
Exit Sub

Err_Go_Click:
MsgBox Err.Description
 
Top