Keying in multiple entries for use as a parameter in query

[KHogwood-Thompson]

I have a form called COSTSUMMARY. On this form I want to have a box of some
kind to collect user input for Job Codes. There could be any number of
entries so some sort of list/combo box would be useful. The values are not
stored in any other table so they will be keyed in.

I want a query to run upon clicking the OK button that will use the values
keyed in as parameters on the field called "JOB REF". The query is called
"Cost Summary Query"

 

[Keith Wilby]

I have a form called COSTSUMMARY. On this form I want to have a box of some
kind to collect user input for Job Codes. There could be any number of
entries so some sort of list/combo box would be useful. The values are not
stored in any other table so they will be keyed in.

I want a query to run upon clicking the OK button that will use the values
keyed in as parameters on the field called "JOB REF". The query is called
"Cost Summary Query"

Top tip: don't use space characters in field names and prefix object names
with a meaningful string (eg "cbo" for a combo box).

Create a select query on your job ref field that returns unique values and
excludes nulls. Use this query as the combo box's row source. Set the
combo box "limit to list" property to "yes". In the combo box "on not in
list" event, put the following code:

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select JobRef from qryCostSummary"
Set rs = db.OpenRecordset(strSQL)
With rs
.AddNew
![JobRef] = NewData
.Update
End With
rs.Close
Set rs = Nothing
db.Close
Response = acDataErrAdded

Adjust object and field names as necessary.

The idea is that the user can type a new job ref into the combo box which
will then appear in the drop-down list.

HTH - Keith.
www.keithwilby.com

 

[KARL DEWEY]

One way is to use a calculated field in your query like this --
Form_Input: [Forms]![COSTSUMMARY]![Job Codes]

and in the criteria row put ---
Like "*" & [JOB REF] & "*"

When you enter the job codes separate them with a space.

 

[KHogwood-Thompson]

Thanks Karl,

This works fine when I am running the query without the form, but when I try
to enter the job codes in the form's unbound list box and separate them with
a space, I don't get any results from then query.
--
K Hogwood-Thompson

One way is to use a calculated field in your query like this --
Form_Input: [Forms]![COSTSUMMARY]![Job Codes]

and in the criteria row put ---
Like "*" & [JOB REF] & "*"

When you enter the job codes separate them with a space.

--
KARL DEWEY
Build a little - Test a little

I have a form called COSTSUMMARY. On this form I want to have a box of some
kind to collect user input for Job Codes. There could be any number of
entries so some sort of list/combo box would be useful. The values are not
stored in any other table so they will be keyed in.

I want a query to run upon clicking the OK button that will use the values
keyed in as parameters on the field called "JOB REF". The query is called
"Cost Summary Query"

 

[KHogwood-Thompson]

Thanks for teh advice Keith, I can see the logic on the combo box but the
reason why I did not want to use one of those was that there are 5000+ job
ref and I did not want the user to have to select from that many records.
--
K Hogwood-Thompson

I have a form called COSTSUMMARY. On this form I want to have a box of some
kind to collect user input for Job Codes. There could be any number of
entries so some sort of list/combo box would be useful. The values are not
stored in any other table so they will be keyed in.

I want a query to run upon clicking the OK button that will use the values
keyed in as parameters on the field called "JOB REF". The query is called
"Cost Summary Query"

Top tip: don't use space characters in field names and prefix object names
with a meaningful string (eg "cbo" for a combo box).

Create a select query on your job ref field that returns unique values and
excludes nulls. Use this query as the combo box's row source. Set the
combo box "limit to list" property to "yes". In the combo box "on not in
list" event, put the following code:

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select JobRef from qryCostSummary"
Set rs = db.OpenRecordset(strSQL)
With rs
.AddNew
![JobRef] = NewData
.Update
End With
rs.Close
Set rs = Nothing
db.Close
Response = acDataErrAdded

Adjust object and field names as necessary.

The idea is that the user can type a new job ref into the combo box which
will then appear in the drop-down list.

HTH - Keith.
www.keithwilby.com

 

[KARL DEWEY]

Use a text box.
--
KARL DEWEY
Build a little - Test a little

Thanks Karl,

This works fine when I am running the query without the form, but when I try
to enter the job codes in the form's unbound list box and separate them with
a space, I don't get any results from then query.
--
K Hogwood-Thompson

One way is to use a calculated field in your query like this --
Form_Input: [Forms]![COSTSUMMARY]![Job Codes]

and in the criteria row put ---
Like "*" & [JOB REF] & "*"

When you enter the job codes separate them with a space.

--
KARL DEWEY
Build a little - Test a little

I have a form called COSTSUMMARY. On this form I want to have a box of some
kind to collect user input for Job Codes. There could be any number of
entries so some sort of list/combo box would be useful. The values are not
stored in any other table so they will be keyed in.

I want a query to run upon clicking the OK button that will use the values
keyed in as parameters on the field called "JOB REF". The query is called
"Cost Summary Query"

 

[KHogwood-Thompson]

The combo box works fine, I did not realise that the user could key in the
value and the correct value would be found in the list and displayd, so that
works well.

Thanks for your help.
--
K Hogwood-Thompson

Thanks for teh advice Keith, I can see the logic on the combo box but the
reason why I did not want to use one of those was that there are 5000+ job
ref and I did not want the user to have to select from that many records.
--
K Hogwood-Thompson

I have a form called COSTSUMMARY. On this form I want to have a box of some
kind to collect user input for Job Codes. There could be any number of
entries so some sort of list/combo box would be useful. The values are not
stored in any other table so they will be keyed in.

I want a query to run upon clicking the OK button that will use the values
keyed in as parameters on the field called "JOB REF". The query is called
"Cost Summary Query"

Top tip: don't use space characters in field names and prefix object names
with a meaningful string (eg "cbo" for a combo box).

Create a select query on your job ref field that returns unique values and
excludes nulls. Use this query as the combo box's row source. Set the
combo box "limit to list" property to "yes". In the combo box "on not in
list" event, put the following code:

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select JobRef from qryCostSummary"
Set rs = db.OpenRecordset(strSQL)
With rs
.AddNew
![JobRef] = NewData
.Update
End With
rs.Close
Set rs = Nothing
db.Close
Response = acDataErrAdded

Adjust object and field names as necessary.

The idea is that the user can type a new job ref into the combo box which
will then appear in the drop-down list.

HTH - Keith.
www.keithwilby.com