loop through fields

[bdjb]

Hello,
I'm using access XP, I have a query that does an insert when a button is clicked, but only on the current records from the form. How can I get it to insert all of the data from the form? Anyone able to point me in the direction of how to get it to loop?

Here's the code from the query

INSERT INTO Training ( employee_number, last_name, first_name, job_title, bpml, track, division )
VALUES (Forms!frmComboTest!employee, Forms!frmComboTest!last_name, Forms!frmComboTest!first_name, Forms!frmComboTest!field11, Forms!frmComboTest!combo11, Forms!frmComboTest!track, Forms!frmComboTest!cbolookup);


What happens if you get scared half to death twice?

Bob

 

[dhookom]

A form doesn't have or store data. It only displays data from tables and queries. I expect you could use the form's record source as the source for an append query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bdjb]

If I click on the button, it will append the "active" row's data to the table, but I need to get all of the rows.I don't think I can use the form's record source, as the append query also pulls data from another combobox that the end user selects from.

What happens if you get scared half to death twice?

Bob

 

[dhookom]

You can add an expression to an append query like:
MyOtherColumn: Forms!frmComboTest!combo11

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bdjb]

Thanks for the reply. All of the code works fine right now for just one record, but my criteria pulls in multiple records, and I need to figure out how to loop through them all, and append them to another table. Is that possible?

What happens if you get scared half to death twice?

Bob

 

[dhookom]

Where does this come from "but my criteria pulls in multiple records"? What criteria? Can you be more specific about the record source and/or filter of your form?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bdjb]

Sorry about that. the data comes from sql code that looks at a text box and uses it's contents to filter data from a table. Another piece of the data I need to append comes from a combobox that feeds from a query.

What happens if you get scared half to death twice?

Bob

 

[dhookom]

You should be able to grab the form's record source SQL as well as the value of the text box as well as the value of the combo box and build an update query to execute.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]