moving records from continuos form to table

[knaya]

it's always one problem or the other.. so i have a continuos form which contains 3 fields..e.g fname, lname, and ssn..now this continuos form is a subform.. what i would like to do when i click a button, i would like the s.s.n number/numbers (depending on how many) and date field from the main form to transfer to a table which has has 2 colums, s.s.n and date.. with what i have now, when i click the button e.g my subform looks something like this:
345765877 j fidler
364778988 m wild
897654333 p manning

when i click the button i would like for all ssn + the date to transfer to the table so it can look like this:
345765877 12/03/03
364778988 12/03/03
897654333 12/03/03

instead, all i'm getting when i press my button is the first row:
345765877 12/03/03


plus, how if u have a price field on a continuos form, how can u calculate total(on main form).. i have no idea how to do this....thanks guys, any help is greatly appreciated..

 

[knaya]

someone helpppp.. this is like the final part to making this database work and i cant figure it to save my lifee.......

 

[MakeItSo]

Hi knaya:
Use sth like this in the Click() event of your save button:

DoCmd.RunSQL "Update main table SET ssn=" & Me!ssn & ", datefield= #" & Me!datefield & "# WHERE [ID]=" & Me!Parent!ID

Adapt the table and field names respectively to yours as well as the matching criteria (Me!Parent!ID). This is the unique ID from your main table/main form.

Does that help?
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[knaya]

forgive me for sounding so ignorant but i dont understand, lets say table name is transaction with 2 fields id and date and on the form i have productid(as txtbox name on continuos form) and currentdate(txtbox on main form) so this is what it would look like, please correct me...

DoCmd.RunSQL "Update transaction SET productid=" & Me!product & ", date= #" & Me!Text1 & "# WHERE [parentID]=" & Me!Parent!parentID

helppppppppppp:-(

 

[MakeItSo]

Hi knaya - calm down.. You'll get it work, I'm sure. I've read some of your threads and you already posted the answer yourself before I could... :-D
(That's why I waited before I answered here...)

1)Adapt your UPDATE to this:
DoCmd.RunSQL "Update transaction SET date= #" & Me!Parent!currentdate & "# WHERE id=" & Me!productid

2) You should rename your table field "date" to something else, since "date" is a reserved word in access and might cause problems in use.

3) My former UPDATE was kinda non-sense: You don't have to update the id if a record for this id already exists.

If you want to insert new records with your subform, you must run an INSERT INTO - statement:

DoCmd.RunSQL "INSERT INTO transaction (id, date) VALUES (" & me!productid & ", #" & me!currentdate & #)"

Hope this wasn't too confusing.
CU (read you)
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos