Simplest way to run 2 queries invoked by a form?

  • Thread starter Thread starter Jeremy
  • Start date Start date
J

Jeremy

Here's the requirement.

User needs to be able to enter 2 items of data onto a form and press a
button. The code behind the button needs to check that both fields have
a valid entry (one is a pick list of values 1-3 the other is a date) and
then execute 2 SQL statements.

1) do i put the sql statements into individual queries?
2) presuming I use the "on click" method on the button, how would I code
references to the two fields on the orm?
3) within the sql, how do I refer to values of the two fields on the
form?

This is part of a voluntary project - not my area of expertise at all,
so appreciate some simple code examples or pointers to where I can get a
primer in how to do this elementary stuff with Access.

BTW it is Access 2003.

TIA
 
Here's the requirement.

User needs to be able to enter 2 items of data onto a form and press a
button. The code behind the button needs to check that both fields have
a valid entry (one is a pick list of values 1-3 the other is a date) and
then execute 2 SQL statements.

1) do i put the sql statements into individual queries?
Click to expand...

That depends. What do the two queries DO? Are they update queries
updating an existing record in a table? Same table? different tables?
Or Append queries adding a new record (or records)?
2) presuming I use the "on click" method on the button, how would I code
references to the two fields on the orm?
Me!Controlname

3) within the sql, how do I refer to values of the two fields on the
form?
Click to expand...

Again... Me!ControlName. If you're building the SQL string just
concatenate that reference.
This is part of a voluntary project - not my area of expertise at all,
so appreciate some simple code examples or pointers to where I can get a
primer in how to do this elementary stuff with Access.
Click to expand...

This isn't elementary. Using unbound controls and code to update
tables is fairly advanced, and is USUALLY not necessary! Typically
you'ld simply bind the controls to fields in a table to update them.

If the first field is a pick list (a "Combo Box" in Access jargon) you
can set its Limit to List property to True, and the user won't be ABLE
to select an invalid entry, presuming that the combo's row source
contains only valid entries. The date field could have a date Input
Mask, and you could use its BeforeUpdate event to check for a valid
date range.

Do you have some particular reason for wanting to do it the hard way?

John W. Vinson[MVP]
 
John Vinson said:
That depends. What do the two queries DO? Are they update queries
updating an existing record in a table? Same table? different tables?
Or Append queries adding a new record (or records)?
Click to expand...

One inserts a row into a master table.
The second then inserts a variable number of rows into a detail table
(FK to the master).

So 2 SQL statements are required.
This isn't elementary. Using unbound controls and code to update
tables is fairly advanced, and is USUALLY not necessary! Typically
you'ld simply bind the controls to fields in a table to update them.
Click to expand...

Sure - but in this case the 2 fields being entered are parameters to
drive a process.
If the first field is a pick list (a "Combo Box" in Access jargon) you
can set its Limit to List property to True, and the user won't be ABLE
to select an invalid entry, presuming that the combo's row source
contains only valid entries. The date field could have a date Input
Mask, and you could use its BeforeUpdate event to check for a valid
date range.
Click to expand...

OK thanks
Do you have some particular reason for wanting to do it the hard way?
Click to expand...

Oh absolutely not. But I don't think what I am asking is for how to it
the hard way - if there is something simpler, I would certainly far
prefer to follow that route.

But I need to refer to the variables from the form in the SQL statements
for sure.

cheers
 
But I need to refer to the variables from the form in the SQL statements
for sure.
Click to expand...

OK, let's see if some sample code gets you on the right road:

Dim strSQL As String
strSQL = "INSERT INTO MainTable (Numfield, Textfield) VALUES (" _
& Me!txtFirstField & ", '" & Me!txtSecondField & "');"
DoCmd.RunSQL strSQL

in the Click event [Event Procedure] of your command button.

John W. Vinson[MVP]
 
Back
Top