Append Queries

[tinker]

Hiya,
I've posted a longer version of this msg before but i think it might of been too long winded.

Is it possible to run an append query which is activated by a macro when an item in a forms datasheet is selected. I want to append the CID field from my consuables table into Consumable Id in Main table (with a criteria that says Where CID is the same as the the current records CID Field on the form Consumables)and HID field from my Hardware table into Hardware ID in my Main table (With a criteria that says HID is the same as the record selected HID Field on the subform Harware which is a datasheet). (This should hopefully create a new row in the main table with an autonumber id field)

Thanks, hope this makes sense.

 

[araskas]

Can you re phrase your question ?

 

[tinker]

I realise i never explained it very well.

I need to run an append query which appends two fields from different tables into a seperate table. The problem ive got is that for the two fields which i need to copy accross, i want to put a criteria on them.
The first fields criteria has to be were it equals a field called CID on the form. The second fields criteria has to be were it equals a field called HID on the subform.

Whats the best way of activating this query?

Thank you

 

[simongallop]

Presume that on your form you have 2 controls, one showing CID and on showing HID. For this answer I will say that the controls are text boxes called txtCID and txtHID.

I am guessing that you do not want to transfer any other data as that is already stored in CID/HID tables.


Sub MoveData()
Dim rs as Recordset
Dim CIDNum, HIDNum

set rs = CurrentDb.OpenRecordset("MainTableName")

CIDNum = me.txtCID.value
HIDNum = me.txtHID.value


rs.addnew
rs("CIDColumnName") = CIDNum
rs("HIDColumnName") = HIDNum
rs.update

rs.close

end sub

Suggest that you attach the code to a button on the form so that the user updates the table when they are ready

HTH