Multiple queries to one table

  • Thread starter Thread starter Duftopia
  • Start date Start date
D

Duftopia

I have 1 table that I would like to mke mu;tiple queries on
different fields that when found loads a subform that can
be edited.

Querys:

1) By id
2) By name
3) By address
4) By city

table has above fields with additional 10 fields..

If the user enters id the subform will be looked up by id,
if he/she enters name the subform will be looked up by name
(multiple), same for address and city

I keep getting error: You tried to call Update or
CancelUpdate or attempted to update a Field in a recordset
without first calling AddNew or Edit. (Error 3020)

I tried linking the form to the subform but bound fields
caused db corruption, I tried using a global variable to
link parent to child but that failed to accept.

any examples or suggestions?
 
I have 1 table that I would like to mke mu;tiple queries on
different fields that when found loads a subform that can
be edited.

Querys:

1) By id
2) By name
3) By address
4) By city
Click to expand...

Hos about ONE query, with four criteria?

You can do this by having four controls (textboxes or, better, combo
boxes), UNBOUND, on a form. Let's say it's frmCrit with combos cboID
and cboCity, and textboxes txtName and txtAddress.

You could have a Query with a WHERE clause such as

WHERE ([ID] = Forms!frmCrit!cboID OR Forms!frmCrit!cboID IS NULL)
AND ([Name] LIKE Forms!frmCrit!txtName & "*" OR Forms!frmCrit!txtName
IS NULL
AND ([Address] LIKE "*" & Forms!frmCrit!txtAddress & "*")
AND ([City] = Forms!frmCrit!cboCity OR Forms!frmCrit!cboCity IS NULL)

Put a button on frmCrit to open the form based on this query and
you'll be able to enter any one or a combination of criteria - or no
criteria at all to see all the records in the table.
 
further to duftopia's enquriy, i 've a similar question.
i 've a book list union database (which link 2 excel db sources)
with column A, B, C, D, .........
such that :
column
A B C D E
... be ... .. ...
... be .. ... ...
... ce ... .. . ...
... de .. ... ...

is it possible to make an query with input box, so that i can enter more
than one criteria to select the type of row data i want ?! let's say, i
enter (be, & ce & de), then it can select the rows of data with be, ce or
de in column B.
Thanks in advance

Toby W.


John Vinson said:
I have 1 table that I would like to mke mu;tiple queries on
different fields that when found loads a subform that can
be edited.

Querys:

1) By id
2) By name
3) By address
4) By city
Click to expand...

Hos about ONE query, with four criteria?

You can do this by having four controls (textboxes or, better, combo
boxes), UNBOUND, on a form. Let's say it's frmCrit with combos cboID
and cboCity, and textboxes txtName and txtAddress.

You could have a Query with a WHERE clause such as

WHERE ([ID] = Forms!frmCrit!cboID OR Forms!frmCrit!cboID IS NULL)
AND ([Name] LIKE Forms!frmCrit!txtName & "*" OR Forms!frmCrit!txtName
IS NULL
AND ([Address] LIKE "*" & Forms!frmCrit!txtAddress & "*")
AND ([City] = Forms!frmCrit!cboCity OR Forms!frmCrit!cboCity IS NULL)

Put a button on frmCrit to open the form based on this query and
you'll be able to enter any one or a combination of criteria - or no
criteria at all to see all the records in the table.
Click to expand...
 
-----Original Message-----
I have 1 table that I would like to mke mu;tiple queries on
different fields that when found loads a subform that can
be edited.

Querys:

1) By id
2) By name
3) By address
4) By city

table has above fields with additional 10 fields..

If the user enters id the subform will be looked up by id,
if he/she enters name the subform will be looked up by name
(multiple), same for address and city

I keep getting error: You tried to call Update or
CancelUpdate or attempted to update a Field in a recordset
without first calling AddNew or Edit. (Error 3020)

I tried linking the form to the subform but bound fields
caused db corruption, I tried using a global variable to
link parent to child but that failed to accept.

any examples or suggestions?


.
Click to expand...
This information was very usefull, however upon entering
the Sql parameters into access it was split up into the
criteria bars for the query and now selecting ID results in
returning ALL records.

I think this is due to the "cboname "*" or is NULL"
selection which selects all records when the name combo box
is not entered just the ID.

So I am still stuck with the primary problem, I need to
selected individual field parameters seperate from each
other and lookup records based on that selection, and I
don't want to create a unique query for each field
selection with a special criteria.

Thank You
 
This information was very usefull, however upon entering
the Sql parameters into access it was split up into the
criteria bars for the query and now selecting ID results in
returning ALL records.
Click to expand...

Please post the SQL. This could be something as simple as a misplaced
parenthesis.
 
Back
Top