Inserting a new record in a table automatically 1

[Gavroche00]

Have no idea how hard this is or if it can be done:

In my table credit, I issue an amount linked to a person via a form. For example, in frmCredit, I have employee number, amount and date. Once I fill the record, I would like a record to be filled in table debit with the same employee number, the same amount and 7 added to the date. Can this be done relatively painelessly?

Thank you

David

 

[flyover789]

assuming you have a unique id in your tbl_credit, you could issue an sql insert in the on_click of a button (not tested):

private sub btn_PostToDebit_click()
dim sql as string
me.refresh 'saves the current record
docmd.runsql "insert into tbl_debit([employee number], " & _ 
"[amount], [duedate] select t.[employee number], " & _
 "t.[amount], t.[duedate]+7 from tbl_credit t " & _
"where t.id = forms!yourform!txt_id;"
end sub

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[flyover789]

the line:
dim sql as string
is not needed

Martin Serra Jr.

http://www.mserrasystems.de

 

[flyover789]

aargh, correction (closing bracket missing, naming of field date in tbl_credit):

private sub btn_PostToDebit_click()
me.refresh 'saves the current record
docmd.runsql "insert into tbl_debit([employee number], " & _ 
"[amount], [duedate]) select t.[employee number], " & _
"t.[amount], t.[date]+7 from tbl_credit t " & _
"where t.id = forms!yourform!txt_id;"
end sub

btw: you should avoid using reserved words (in this case date) as fieldnames.

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[Gavroche00]

Martin,

I am getting a syntax error when I try to run your code.

Martin, what does t stands for? Am I supposed to replace t with something else?


David

 

[PHV]

Replace this:
"where t.id = forms!yourform!txt_id;"
By this (if ID is numeric):
"where t.id = " & forms!yourform!txt_id & ";"
Or by this (if ID is defined as Text in the table):
"where t.id = '" & forms!yourform!txt_id & "';"

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