changing record cource in queries

[postman]

How to?
At the moment I have 6 linked tables that have data I need to search &
extract from. To do the searching I have 6 'types' of queries & forms to
each tables record eg: 6 select queries, 6 append queries, 6 delete queries,
6 delete duplicates queries, 6 forms, 6 subforms..etc etc.
I would like to have just one all purpose query 'type' and form/subform to
do all this repetitive work from.
The 6 linked tables have the same field names just different records.
Can I change the record source of the queries through a combobox on a form,
thus eliminating the need for 6 of everything, so I would have just one type
of query and form, I can then select the record source ( the linked table
names) through the combobox.
It's a bit beyond my comprehension at this stage, could someone steer me in
the right direction.
So for I have created a table with one field; typed in the list of the
linked table names, for the combo box to use as the record source selector.
How do I use that in the query as the record source...

Thanks.

 

[Marshall Barton]

At the moment I have 6 linked tables that have data I need to search &
extract from. To do the searching I have 6 'types' of queries & forms to
each tables record eg: 6 select queries, 6 append queries, 6 delete queries,
6 delete duplicates queries, 6 forms, 6 subforms..etc etc.
I would like to have just one all purpose query 'type' and form/subform to
do all this repetitive work from.
The 6 linked tables have the same field names just different records.
Can I change the record source of the queries through a combobox on a form,
thus eliminating the need for 6 of everything, so I would have just one type
of query and form, I can then select the record source ( the linked table
names) through the combobox.
It's a bit beyond my comprehension at this stage, could someone steer me in
the right direction.
So for I have created a table with one field; typed in the list of the
linked table names, for the combo box to use as the record source selector.
How do I use that in the query as the record source...

Well. you could modify the saved query's SQL property, but
that's kind of roundabout when you can just set the form's
record source to the SQL statement in the combo box's
AfterUpdate event:

Me.RecordSource = "SELECT ... FROM [" & me.combo & "] ..."
or
db.Execute "DELETE * FROM [" & me.combo & "] ..."

 

[postman]

Sorry should have posted this initially:
The combobox event procedure:
Private Sub Combo5_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The UpdateQueries SQL for manual input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE = 10.99, dB_DVD_Prices.CBPRICE
= 5.7, dB_DVD_Prices.TBPRICE = 7
WHERE (((dB_DVD_Prices.NPRICE) Between 13 And 13.99));

The UpdateQueries SQL for Form Input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![SPRICE], dB_DVD_Prices.CBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![CBPRICE], dB_DVD_Prices.TBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![TPRICE]
WHERE (((dB_DVD_Prices.NPRICE)=[Forms]![Frm_NEW_PRICE_RANGE]![Combo5]));

The first above Manual Input works but I need to re-enter all the numbers to
match the table row's between xx And xxx lines in query design view then run
it. The numbers are scaler & depend on the between numbers, at moment I have
about 60 between criterias and 3 update field rows to match each between
criteria.

BUT when using the form the update query won't read the form's combobox
(between xxx And xxx)at all & throws the errorata type mismatch in
criteria expression. Just won't pick it up. So I'm stuck at that point and
manually entering all these numbers.

I'll do as you suggest with the Form's AfterUpdate event code.
Do I replace the line:rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
With the on suggested by yourself, or add it in under?


Thanks.

At the moment I have 6 linked tables that have data I need to search &
extract from. To do the searching I have 6 'types' of queries & forms to
each tables record eg: 6 select queries, 6 append queries, 6 delete
queries,
6 delete duplicates queries, 6 forms, 6 subforms..etc etc.
I would like to have just one all purpose query 'type' and form/subform to
do all this repetitive work from.
The 6 linked tables have the same field names just different records.
Can I change the record source of the queries through a combobox on a
form,
thus eliminating the need for 6 of everything, so I would have just one
type
of query and form, I can then select the record source ( the linked table
names) through the combobox.
It's a bit beyond my comprehension at this stage, could someone steer me
in
the right direction.
So for I have created a table with one field; typed in the list of the
linked table names, for the combo box to use as the record source
selector.
How do I use that in the query as the record source...

Well. you could modify the saved query's SQL property, but
that's kind of roundabout when you can just set the form's
record source to the SQL statement in the combo box's
AfterUpdate event:

Me.RecordSource = "SELECT ... FROM [" & me.combo & "] ..."
or
db.Execute "DELETE * FROM [" & me.combo & "] ..."

 

[Marshall Barton]

Sorry should have posted this initially:
The combobox event procedure:
Private Sub Combo5_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The UpdateQueries SQL for manual input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE = 10.99, dB_DVD_Prices.CBPRICE
= 5.7, dB_DVD_Prices.TBPRICE = 7
WHERE (((dB_DVD_Prices.NPRICE) Between 13 And 13.99));

The UpdateQueries SQL for Form Input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![SPRICE], dB_DVD_Prices.CBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![CBPRICE], dB_DVD_Prices.TBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![TPRICE]
WHERE (((dB_DVD_Prices.NPRICE)=[Forms]![Frm_NEW_PRICE_RANGE]![Combo5]));

The first above Manual Input works but I need to re-enter all the numbers to
match the table row's between xx And xxx lines in query design view then run
it. The numbers are scaler & depend on the between numbers, at moment I have
about 60 between criterias and 3 update field rows to match each between
criteria.

BUT when using the form the update query won't read the form's combobox
(between xxx And xxx)at all & throws the errorata type mismatch in
criteria expression. Just won't pick it up. So I'm stuck at that point and
manually entering all these numbers.

I'll do as you suggest with the Form's AfterUpdate event code.
Do I replace the line:rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
With the on suggested by yourself, or add it in under?

The combo box's AfterUpdate event that you posted above is
OK (except it should be RecordsetClone, not
Recordset.Clone), if you want to position the form's current
record to the matching value. What I suggested earlier
would filter the form's data to only contain that one
record, which is what I thought you were asking for.

I don't understand what you're talking about with the UPDATE
query not "reading" the form's combo box. As posted, the
query's WHERE clause looks perfectly normal and I would
expect it to work as long as the form is open.

Without further explanation, I have no idea how a Between
comes into the picture, but a type mismatch error typically
means that you are missing some quotes around a text value,
that you have extra quotes around a numeric value, or maybe
you are missing #s around a date value.

 

[postman]

Disregard last post should have been posted to another question
Sorry.

Sorry should have posted this initially:
The combobox event procedure:
Private Sub Combo5_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The UpdateQueries SQL for manual input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE = 10.99,
dB_DVD_Prices.CBPRICE = 5.7, dB_DVD_Prices.TBPRICE = 7
WHERE (((dB_DVD_Prices.NPRICE) Between 13 And 13.99));

The UpdateQueries SQL for Form Input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![SPRICE], dB_DVD_Prices.CBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![CBPRICE], dB_DVD_Prices.TBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![TPRICE]
WHERE (((dB_DVD_Prices.NPRICE)=[Forms]![Frm_NEW_PRICE_RANGE]![Combo5]));

The first above Manual Input works but I need to re-enter all the numbers
to match the table row's between xx And xxx lines in query design view
then run it. The numbers are scaler & depend on the between numbers, at
moment I have about 60 between criterias and 3 update field rows to match
each between criteria.

BUT when using the form the update query won't read the form's combobox
(between xxx And xxx)at all & throws the errorata type mismatch in
criteria expression. Just won't pick it up. So I'm stuck at that point and
manually entering all these numbers.

I'll do as you suggest with the Form's AfterUpdate event code.
Do I replace the line:rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
With the on suggested by yourself, or add it in under?


Thanks.

At the moment I have 6 linked tables that have data I need to search &
extract from. To do the searching I have 6 'types' of queries & forms to
each tables record eg: 6 select queries, 6 append queries, 6 delete
queries,
6 delete duplicates queries, 6 forms, 6 subforms..etc etc.
I would like to have just one all purpose query 'type' and form/subform
to
do all this repetitive work from.
The 6 linked tables have the same field names just different records.
Can I change the record source of the queries through a combobox on a
form,
thus eliminating the need for 6 of everything, so I would have just one
type
of query and form, I can then select the record source ( the linked table
names) through the combobox.
It's a bit beyond my comprehension at this stage, could someone steer me
in
the right direction.
So for I have created a table with one field; typed in the list of the
linked table names, for the combo box to use as the record source
selector.
How do I use that in the query as the record source...

Well. you could modify the saved query's SQL property, but
that's kind of roundabout when you can just set the form's
record source to the SQL statement in the combo box's
AfterUpdate event:

Me.RecordSource = "SELECT ... FROM [" & me.combo & "] ..."
or
db.Execute "DELETE * FROM [" & me.combo & "] ..."

 

[postman]

I'm still working on this query & form reduction to one all purpose. Just
need to think about your suggestion a bit more, I'm wondering if a second
query that picks up the form combobox that reads the 6 different linked
tables would work. The other queries I use could then query that
query?...I'll also test this.
Apologies again for the wrong post.

Disregard last post should have been posted to another question
Sorry.

Sorry should have posted this initially:
The combobox event procedure:
Private Sub Combo5_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The UpdateQueries SQL for manual input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE = 10.99,
dB_DVD_Prices.CBPRICE = 5.7, dB_DVD_Prices.TBPRICE = 7
WHERE (((dB_DVD_Prices.NPRICE) Between 13 And 13.99));

The UpdateQueries SQL for Form Input:
UPDATE dB_DVD_Prices SET dB_DVD_Prices.SPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![SPRICE], dB_DVD_Prices.CBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![CBPRICE], dB_DVD_Prices.TBPRICE =
[Forms]![Frm_NEW_PRICE_RANGE]![TPRICE]
WHERE (((dB_DVD_Prices.NPRICE)=[Forms]![Frm_NEW_PRICE_RANGE]![Combo5]));

The first above Manual Input works but I need to re-enter all the numbers
to match the table row's between xx And xxx lines in query design view
then run it. The numbers are scaler & depend on the between numbers, at
moment I have about 60 between criterias and 3 update field rows to match
each between criteria.

BUT when using the form the update query won't read the form's combobox
(between xxx And xxx)at all & throws the errorata type mismatch in
criteria expression. Just won't pick it up. So I'm stuck at that point
and manually entering all these numbers.

I'll do as you suggest with the Form's AfterUpdate event code.
Do I replace the line:rs.FindFirst "[NP RANGE] = '" & Me![Combo5] & "'"
With the on suggested by yourself, or add it in under?


Thanks.

postman wrote:
At the moment I have 6 linked tables that have data I need to search &
extract from. To do the searching I have 6 'types' of queries & forms to
each tables record eg: 6 select queries, 6 append queries, 6 delete
queries,
6 delete duplicates queries, 6 forms, 6 subforms..etc etc.
I would like to have just one all purpose query 'type' and form/subform
to
do all this repetitive work from.
The 6 linked tables have the same field names just different records.
Can I change the record source of the queries through a combobox on a
form,
thus eliminating the need for 6 of everything, so I would have just one
type
of query and form, I can then select the record source ( the linked
table
names) through the combobox.
It's a bit beyond my comprehension at this stage, could someone steer me
in
the right direction.
So for I have created a table with one field; typed in the list of the
linked table names, for the combo box to use as the record source
selector.
How do I use that in the query as the record source...


Well. you could modify the saved query's SQL property, but
that's kind of roundabout when you can just set the form's
record source to the SQL statement in the combo box's
AfterUpdate event:

Me.RecordSource = "SELECT ... FROM [" & me.combo & "] ..."
or
db.Execute "DELETE * FROM [" & me.combo & "] ..."

 

[postman]

Really having trouble with this; I have built a new select query to do the
job of
the 6 other "select queries" that read the 6 different linked tables that
have 6 fields each of the same name. And a new form to do the work of the 5
others, the new form reads the new select query.
But without a table in the design area I cannot build anything...how do I go
about this.
The field names are: TITLE, FORMAT, NPRICE, SPRICE, CPRICE, TPRICE,
COMMENTS.

Do I build like I would an expression etc for the 6 fields.:
Filed: TITLE: [TITLE] FORMAT: [FORMAT] etc,etc.
Table: can't code build here, it needs a table to ref.
Sort: blank
Show: blank
Criteria: Like ([Forms]![Frm_TEST SEARCH FORM]![Input01]) & "*"


Sorry again Marsh for the confusion.
Thanks.

Disregard last post should have been posted to another question
Sorry.

postman wrote:
At the moment I have 6 linked tables that have data I need to search &
extract from. To do the searching I have 6 'types' of queries & forms to
each tables record eg: 6 select queries, 6 append queries, 6 delete
queries,
6 delete duplicates queries, 6 forms, 6 subforms..etc etc.
I would like to have just one all purpose query 'type' and form/subform
to
do all this repetitive work from.
The 6 linked tables have the same field names just different records.
Can I change the record source of the queries through a combobox on a
form,
thus eliminating the need for 6 of everything, so I would have just one
type
of query and form, I can then select the record source ( the linked
table
names) through the combobox.
It's a bit beyond my comprehension at this stage, could someone steer me
in
the right direction.
So for I have created a table with one field; typed in the list of the
linked table names, for the combo box to use as the record source
selector.
How do I use that in the query as the record source...


Well. you could modify the saved query's SQL property, but
that's kind of roundabout when you can just set the form's
record source to the SQL statement in the combo box's
AfterUpdate event:

Me.RecordSource = "SELECT ... FROM [" & me.combo & "] ..."
or
db.Execute "DELETE * FROM [" & me.combo & "] ..."

 

[Marshall Barton]

Really having trouble with this; I have built a new select query to do the
job of
the 6 other "select queries" that read the 6 different linked tables that
have 6 fields each of the same name. And a new form to do the work of the 5
others, the new form reads the new select query.
But without a table in the design area I cannot build anything...how do I go
about this.
The field names are: TITLE, FORMAT, NPRICE, SPRICE, CPRICE, TPRICE,
COMMENTS.

Do I build like I would an expression etc for the 6 fields.:
Filed: TITLE: [TITLE] FORMAT: [FORMAT] etc,etc.
Table: can't code build here, it needs a table to ref.
Sort: blank
Show: blank
Criteria: Like ([Forms]![Frm_TEST SEARCH FORM]![Input01]) & "*"

As I tried to explain in my first response, you can not
create this query in the query design window.

You must create the query's SQL statement in VBA code and
assign the SQL string to the form's RecordSource property at
runtime.

strSQL "SELECT TITLE, FORMAT, NPRICE, SPRICE, " _
& "CPRICE, TPRICE, COMMENTS " _
& "FROM [" & strTablename & "] " _
& "WHERE somefield LIKE """ & Me.Input01 & "*"""
Me.RecordSource = strSQL

 

[postman]

Ok, thanks,
I put the code straight in my new queries SQL window...but when saving it
threw an error:
Invalid SQL statement; expected 'DELETE','INSERT','PROCEEDURE','SELECT', or
UPDATE'
And HELP threw something about a reserved word at me: SELECT, UPDATE,
BETWEEN, SET, and INSERT. Don't use reserved words for variable or object
names.

Note: I've taken as standard VBA expression in your code and not where I put
in my actual tables name in: [" & strTablename & "] also "WHERE somefield
LIKE"""
....just checking.

The Table with the list of the Table names to search is tbl_DB_LIST and is
what the combobox (Input2) on the form lists to tell the sql witch table to
look in, and then uses the other combobox's criteria "Like
([Forms]![Frm_TEST SEARCH FORM]![Input01]) & "*"" expression to filter
records in the criteria; would there be a call to that combobox Input 2 in
the sql?

Apologies if I did not give that initially, I really do find all this coding
confusing & oblique yet interesting at the same time.
Thanks.

Really having trouble with this; I have built a new select query to do the
job of
the 6 other "select queries" that read the 6 different linked tables that
have 6 fields each of the same name. And a new form to do the work of the
5
others, the new form reads the new select query.
But without a table in the design area I cannot build anything...how do I
go
about this.
The field names are: TITLE, FORMAT, NPRICE, SPRICE, CPRICE, TPRICE,
COMMENTS.

Do I build like I would an expression etc for the 6 fields.:
Filed: TITLE: [TITLE] FORMAT: [FORMAT] etc,etc.
Table: can't code build here, it needs a table to ref.
Sort: blank
Show: blank
Criteria: Like ([Forms]![Frm_TEST SEARCH FORM]![Input01]) & "*"

As I tried to explain in my first response, you can not
create this query in the query design window.

You must create the query's SQL statement in VBA code and
assign the SQL string to the form's RecordSource property at
runtime.

strSQL "SELECT TITLE, FORMAT, NPRICE, SPRICE, " _
& "CPRICE, TPRICE, COMMENTS " _
& "FROM [" & strTablename & "] " _
& "WHERE somefield LIKE """ & Me.Input01 & "*"""
Me.RecordSource = strSQL

 

[Marshall Barton]

What I posted is VBA code, not SQL, as I tried to explain
before, you will not be able to use the query design window
for this. The code goes somewhere in the form's module,
probably in combo01's AfterUpdate event procedure.

Since you have not explained the names of the fields, I have
made up names for those items that are unknown to me. You
will have to understand the suggested code and replace the
made up names with the ones you are actually using. Please
do not copy/paste any posted code and expect it to work
without you taking the time to understand what it does and
how it works. If you don't do that, it will just lead to
total confusion for both of us.

Your introduction of a second combo box is more than I can
deal with in this thread. It is important to stay focused
on one problem at a time so we can build a solid foundation
before taking off on additional issues.
--
Marsh
MVP [MS Access]

I put the code straight in my new queries SQL window...but when saving it
threw an error:
Invalid SQL statement; expected 'DELETE','INSERT','PROCEEDURE','SELECT', or
UPDATE'
And HELP threw something about a reserved word at me: SELECT, UPDATE,
BETWEEN, SET, and INSERT. Don't use reserved words for variable or object
names.

Note: I've taken as standard VBA expression in your code and not where I put
in my actual tables name in: [" & strTablename & "] also "WHERE somefield
LIKE"""
...just checking.

The Table with the list of the Table names to search is tbl_DB_LIST and is
what the combobox (Input2) on the form lists to tell the sql witch table to
look in, and then uses the other combobox's criteria "Like
([Forms]![Frm_TEST SEARCH FORM]![Input01]) & "*"" expression to filter
records in the criteria; would there be a call to that combobox Input 2 in
the sql?

Apologies if I did not give that initially, I really do find all this coding
confusing & oblique yet interesting at the same time.

Really having trouble with this; I have built a new select query to do the
job of
the 6 other "select queries" that read the 6 different linked tables that
have 6 fields each of the same name. And a new form to do the work of the
5
others, the new form reads the new select query.
But without a table in the design area I cannot build anything...how do I
go
about this.
The field names are: TITLE, FORMAT, NPRICE, SPRICE, CPRICE, TPRICE,
COMMENTS.

Do I build like I would an expression etc for the 6 fields.:
Filed: TITLE: [TITLE] FORMAT: [FORMAT] etc,etc.
Table: can't code build here, it needs a table to ref.
Sort: blank
Show: blank
Criteria: Like ([Forms]![Frm_TEST SEARCH FORM]![Input01]) & "*"

"Marshall Barton" wrote
As I tried to explain in my first response, you can not
create this query in the query design window.

You must create the query's SQL statement in VBA code and
assign the SQL string to the form's RecordSource property at
runtime.

strSQL "SELECT TITLE, FORMAT, NPRICE, SPRICE, " _
& "CPRICE, TPRICE, COMMENTS " _
& "FROM [" & strTablename & "] " _
& "WHERE somefield LIKE """ & Me.Input01 & "*"""
Me.RecordSource = strSQL

 

[postman]

ok it's in the combobox Input02, which lists the names of the tables I want
to select & search, it came back with this error: query must have at least
one destination field.

What I posted is VBA code, not SQL, as I tried to explain
before, you will not be able to use the query design window
for this. The code goes somewhere in the form's module,
probably in combo01's AfterUpdate event procedure.

Since you have not explained the names of the fields, I have
made up names for those items that are unknown to me. You
will have to understand the suggested code and replace the
made up names with the ones you are actually using. Please
do not copy/paste any posted code and expect it to work
without you taking the time to understand what it does and
how it works. If you don't do that, it will just lead to
total confusion for both of us.

Your introduction of a second combo box is more than I can
deal with in this thread. It is important to stay focused
on one problem at a time so we can build a solid foundation
before taking off on additional issues.
--
Marsh
MVP [MS Access]

I put the code straight in my new queries SQL window...but when saving it
threw an error:
Invalid SQL statement; expected 'DELETE','INSERT','PROCEEDURE','SELECT',
or
UPDATE'
And HELP threw something about a reserved word at me: SELECT, UPDATE,
BETWEEN, SET, and INSERT. Don't use reserved words for variable or object
names.

Note: I've taken as standard VBA expression in your code and not where I
put
in my actual tables name in: [" & strTablename & "] also "WHERE somefield
LIKE"""
...just checking.

The Table with the list of the Table names to search is tbl_DB_LIST and is
what the combobox (Input2) on the form lists to tell the sql witch table
to
look in, and then uses the other combobox's criteria "Like
([Forms]![Frm_TEST SEARCH FORM]![Input01]) & "*"" expression to filter
records in the criteria; would there be a call to that combobox Input 2 in
the sql?

Apologies if I did not give that initially, I really do find all this
coding
confusing & oblique yet interesting at the same time.

postman wrote:
Really having trouble with this; I have built a new select query to do
the
job of
the 6 other "select queries" that read the 6 different linked tables
that
have 6 fields each of the same name. And a new form to do the work of
the
5
others, the new form reads the new select query.
But without a table in the design area I cannot build anything...how do
I
go
about this.
The field names are: TITLE, FORMAT, NPRICE, SPRICE, CPRICE, TPRICE,
COMMENTS.

Do I build like I would an expression etc for the 6 fields.:
Filed: TITLE: [TITLE] FORMAT: [FORMAT] etc,etc.
Table: can't code build here, it needs a table to ref.
Sort: blank
Show: blank
Criteria: Like ([Forms]![Frm_TEST SEARCH FORM]![Input01]) & "*"

"Marshall Barton" wrote
As I tried to explain in my first response, you can not
create this query in the query design window.

You must create the query's SQL statement in VBA code and
assign the SQL string to the form's RecordSource property at
runtime.

strSQL "SELECT TITLE, FORMAT, NPRICE, SPRICE, " _
& "CPRICE, TPRICE, COMMENTS " _
& "FROM [" & strTablename & "] " _
& "WHERE somefield LIKE """ & Me.Input01 & "*"""
Me.RecordSource = strSQL

 

[Marshall Barton]

Sorry, but I'm having trouble seeing your monitor from here.
Would you mind posting the code that you are currently
having trouble with.

 

[postman]

Private Sub Input2_AfterUpdate()
strSQL "SELECT TITLE, FORMAT, NPRICE, SPRICE, " _
& "CBPRICE, TBPRICE, COMMENTS " _
& "FROM [" & strTablename & "] " _
& "WHERE somefield LIKE """ & Me.Input01 & "*"""
Me.RecordSource = strSQL

End Sub

Sorry, but I'm having trouble seeing your monitor from here.
Would you mind posting the code that you are currently
having trouble with.
--
Marsh
MVP [MS Access]

ok it's in the combobox Input02, which lists the names of the tables I
want
to select & search, it came back with this error: query must have at least
one destination field.


"Marshall Barton" wrote

 

[Marshall Barton]

I don't know how or what strTableName is assigned a value,
but make sure that value is still available when this
procedure runs.

It does look like you never assigned a value to
strTableName, which implies that you are not using OPTION
EXPLICIT to identify undeclared variables. If that's the
case, I strongly recommend that you add the statement to
every one of your existing modules and set the option to
automatically insert it in all new modules.

You will also need to replace "somefield" with the name of
the field that the criteria is being used against.
--
Marsh
MVP [MS Access]

Private Sub Input2_AfterUpdate()
strSQL "SELECT TITLE, FORMAT, NPRICE, SPRICE, " _
& "CBPRICE, TBPRICE, COMMENTS " _
& "FROM [" & strTablename & "] " _
& "WHERE somefield LIKE """ & Me.Input01 & "*"""
Me.RecordSource = strSQL
End Sub

Sorry, but I'm having trouble seeing your monitor from here.
Would you mind posting the code that you are currently
having trouble with.

 

[postman]

I have no idea what you are talking about, try using instructional ENGLISH
as opposed to techno-babble to communicate. Is this a help forum or not?

I don't know how or what strTableName is assigned a value,
but make sure that value is still available when this
procedure runs.

It does look like you never assigned a value to
strTableName, which implies that you are not using OPTION
EXPLICIT to identify undeclared variables. If that's the
case, I strongly recommend that you add the statement to
every one of your existing modules and set the option to
automatically insert it in all new modules.

You will also need to replace "somefield" with the name of
the field that the criteria is being used against.
--
Marsh
MVP [MS Access]

Private Sub Input2_AfterUpdate()
strSQL "SELECT TITLE, FORMAT, NPRICE, SPRICE, " _
& "CBPRICE, TBPRICE, COMMENTS " _
& "FROM [" & strTablename & "] " _
& "WHERE somefield LIKE """ & Me.Input01 & "*"""
Me.RecordSource = strSQL
End Sub

Sorry, but I'm having trouble seeing your monitor from here.
Would you mind posting the code that you are currently
having trouble with.


postman wrote:
ok it's in the combobox Input02, which lists the names of the tables I
want
to select & search, it came back with this error: query must have at
least
one destination field.


What I posted is VBA code, not SQL, as I tried to explain
before, you will not be able to use the query design window
for this. The code goes somewhere in the form's module,
probably in combo01's AfterUpdate event procedure.

Since you have not explained the names of the fields, I have
made up names for those items that are unknown to me. You
will have to understand the suggested code and replace the
made up names with the ones you are actually using. Please
do not copy/paste any posted code and expect it to work
without you taking the time to understand what it does and
how it works. If you don't do that, it will just lead to
total confusion for both of us.

Your introduction of a second combo box is more than I can
deal with in this thread. It is important to stay focused
on one problem at a time so we can build a solid foundation
before taking off on additional issues.

 

[Marshall Barton]

Sorry, but computer programming is a technological activity
that can not be described unambiguously using common
english. The Help files make a pretty good attempt to
expand on technical terms and often provides an example to
demonstrate a concept. Due to the limited space/time
constraints of newsgroup posts, it is your responsibility to
do the homework to try to understand the "techno babble"
and, if you need a clarification, to then ask a specific,
non-general, question. Newsgroups are not an appropriate
mechanism for providing general instruction or answers to
vague questions.

It may help you make more effective use of these newsgroups
is you reviewed the Netiquette and 10 Commandments sections
at http://www.mvps.org/access/
--
Marsh
MVP [MS Access]

I have no idea what you are talking about, try using instructional ENGLISH
as opposed to techno-babble to communicate. Is this a help forum or not?


"Marshall Barton" wrote

I don't know how or what strTableName is assigned a value,
but make sure that value is still available when this
procedure runs.

It does look like you never assigned a value to
strTableName, which implies that you are not using OPTION
EXPLICIT to identify undeclared variables. If that's the
case, I strongly recommend that you add the statement to
every one of your existing modules and set the option to
automatically insert it in all new modules.

You will also need to replace "somefield" with the name of
the field that the criteria is being used against.

Private Sub Input2_AfterUpdate()
strSQL "SELECT TITLE, FORMAT, NPRICE, SPRICE, " _
& "CBPRICE, TBPRICE, COMMENTS " _
& "FROM [" & strTablename & "] " _
& "WHERE somefield LIKE """ & Me.Input01 & "*"""
Me.RecordSource = strSQL
End Sub


Sorry, but I'm having trouble seeing your monitor from here.
Would you mind posting the code that you are currently
having trouble with.


postman wrote:
ok it's in the combobox Input02, which lists the names of the tables I
want
to select & search, it came back with this error: query must have at
least
one destination field.


What I posted is VBA code, not SQL, as I tried to explain
before, you will not be able to use the query design window
for this. The code goes somewhere in the form's module,
probably in combo01's AfterUpdate event procedure.

Since you have not explained the names of the fields, I have
made up names for those items that are unknown to me. You
will have to understand the suggested code and replace the
made up names with the ones you are actually using. Please
do not copy/paste any posted code and expect it to work
without you taking the time to understand what it does and
how it works. If you don't do that, it will just lead to
total confusion for both of us.

Your introduction of a second combo box is more than I can
deal with in this thread. It is important to stay focused
on one problem at a time so we can build a solid foundation
before taking off on additional issues.