Changing a value in a different table through code on a form 2

[tkemner]

I have 2 Tables i am using

Table1 = COCTL

Fields = Id, CusIdAuto, CusIdLast

This table only has 1 record and should never have more than that it controls the customer number for table COCUS. CusIdAuto is the increment value i want to increment CudIdLast by every time i make a new record in COCUS.

Table2 = COCUS

Fields = CusId

There are more fields than this but there really not relevant to the problem.

I want to run some code beofore insert on a form that contains COCUS record information the code will take the value from Table COCTL field CusIdLast and
CusIdAuto add them together and make that the CusId for COCUS table and then change the value of CusIdLast to what it just set the value of CusId to.

I think just showing you the code will make it more clear.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!CusId = DLookup("CusIdLast", "COCTL") + DLookup("CusIdAuto", "COCTL")
[COCTL]![CusIdLast] = Me!CusId
End Sub

I know this code won't work the first part will work fine it's the last part i have problems with. I just don't know how to set a value in a field that is not the current table(recordset).

Thanks in advance,

Tim

 

[PHV]

You may try this:
DoCmd.RunSQL "UPDATE COCTL SET CusIdLast = " & Me!CusId

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

how about an update query?

DoCmd.RunSQL "UPDATE COCTL SET CustIdLast = " & Me!CustId & " ;"

you may also wish to turn off warning messages using DoCmd.SetWarnings True/False

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tkemner]

Thank you very much worked perfectly

Tim