Query that prompts with a listbox 1

[lck092580]

Hi,

Can anyone help me with a function that prompts the user with a listbox listing some items?

What I need to do is to insert the field of a table into a query.

For example:

select * from contacts
where contacts.[someInput] <> null

I want to incorporate this into a form so the user can just select a listbox and then the bottom returns results.

Thanks.

 

[dhookom]

You can't prompt in a query with a list box. You would need to use a form with a listbox.

"<> Null" doesn't work. "Is Not Null" does work.


Are you asking for a method for a user to select a field and then return all the records where the selected field contains a value?


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]

 

[lck092580]

Hi,

Yup! I need a method that takes in an listbox option and then puts it into my query for a specific field with conditions.

Thanks.

 

[dhookom]

If I understand correctly, you can have a list box on a form with a type of Field List. Then use the after update event of the list box with code like:

   Me.RecordSource = "SELECT * FROM Contacts " & _
       "WHERE [" & Me.lboFieldList & "] Is Not Null"

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]

 

[lck092580]

Thanks.

I'm trying to simplify it right now but doing this:

Created a combo box with text values.

Created a subform at the bottom.

Basically what I'm trying to do now is to just execute a query in the subform depending on what is selected in the combo box.

So lets say the user selects "firstname" in the combo box, the bottom should execute the query:

select contacts."firstname"
from contacts

Then display the details. My query is a bit more complex than the above but is similar. How do I link the subform at the bottom to the combo box above if they're not linked via PK or FK? I just want to grab the string value in the combo box and insert it into my query.

Thanks.

 

[dhookom]

Your question is a bit like a moving target. It would help to understand your actual requirements from the get-go.

Assuming you have a subform control named "subformCtlName" and the record source of the subform is a sql statement like:
Record Source: SELECT FirstName as TheField FROM Contacts;

Use code in the after update of the list box like:

   Me.SubformCtlName.Form.RecordSource = "SELECT [" & Me.lboFieldList  & "] As TheField FROM Contacts"

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]

 

[lck092580]

Hi Duane,

Sorry I'm not really good at this.

What do you mean by subform control?

I have a ComboBox that lists dates in text form that are pulled from a table.

I then have a subform called "Who's available for when" which uses the query:

SELECT contact.ID, contact.RespondentID, contact.FirstName, contact.LastName, FROM contact LEFT JOIN availability ON Availability = availability.ID
WHERE ((([availability].[When?]) Is Not Null And ([availability].[When?])<>'NOT AVAILABLE AT THESE TIMES'));

I'm trying to put a line into the two "When"(s) so that it grabs input from the combobox.

I know the queries' a bit odd but I'm forced to work with it because it came from survey monkey and the format's terrible.

Thanks for the continuing help. I really appreciate it.

 

[dhookom]

I don't see a date field in the subform record source. If there is a date field in there and it contains only the date value, you can use the link master/child properties of the subform control.

Link Master: [Name of your combo box]
Link Child: [Name of your date field from availability table]


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]

 

[lck092580]

Hi Duane,

The date field is actually the:

[When?] that I put in so the user can input the date.

WHERE ((([availability].[When?]) Is Not Null And ([availability].[When?])<>'NOT AVAILABLE AT THESE TIMES'));

 

[dhookom]

You can't have a date field that stores 'NOT AVAILABLE AT THESE TIMES'.

Do you have a field named [When?] in your table named [Availability]? What is the type of field?

If you simply want to filter a subform based on a value selected in a combo box (or other control) on the main form, do as I suggested in my post of #18 Jul 06 16:08#.

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]

 

[lck092580]

Hi Duane,

That date field is actually a text field. I'm forced to work with a text field because survey monkey exported the date field with values that do are not dates.

The table availability have fields: Aug1st, Aug2nd, Aug3rd, etc.

So as an example, if I wanted to query to see who's NOT available on Aug1st, I would write something like:

select * from contacts where availability = (select id from availability_table where aug1st <> "not available at this time")

I know the tables are poorly designed. I'm just now stuck working with it because that's the export.

I like your after update idea. What I'd like to know if there's a way to grab what's in the combo box.

Here's an example of the form:

[Combo Box] <-- Choices include, "Aug1st", "Aug2nd", "Aug3rd", and so on. (these are all just text fields)

[Sub Form] <--- I want this subform to show a query that grabs whatever was selected in the combo box.

So, I would want to execute something like:

Select * from contacts where availability = (select id from availability_dates where ***grabbedFromComboBox*** = "available")

Thanks and I really appreciate your continuing help.

 

[dhookom]

So, you have field names like Aug1st, Aug2nd,... that are text.

What types of values are stored in the fields? Are there only two possible values: "Available" and "not available at this time"?

Could you share some sample records (significant fields) and how you expect to query/display them?

Can you provide some control names such as your combo box and subform names?



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]

 

[lck092580]

Hi Duane,

Yup! The fields are actual dates.

The possible values are either the date value, such as MON AUG 13, TUES AUG 14, or NOT AVAILABLE AT THIS TIME.

My thoughts on how to try and figure out who is available and when, is to just do a query that searches for not null values and where the value is not: "NOT AVAILABLE AT THIS TIME".

I basically took the spreadsheet and created two tables.

Contacts table with an additional Availability field that's a FK for the table Availability to link them up.

Contacts has the following fields:
- ID
- Firstname
- Lastname
- Email
- Availability

Availability
- ID
- Mon AUG 13
- TUES AUG 14
etc etc...

So, each contact will have a link to the availability table with each of those date fields filled out with either the corresponding date or "not available at this time".

So to put an example, lets say we have 2 records:

Contact
1, John, Smith, js@hotmail.com, 1
2, David, Brown, db@hotmail.com, 2

Availability
1, Not available at this time, TUES AUG 14
2, MON AUG 13, Not available at this time

So what I then did was create another table called Dates. Then created a field called Date and put in MON AUG 13, TUES AUG 14, WED AUG 15, etc.

Then I created a form in design mode called: Availability.

Then I created a combo box called combo0.

Then I created a subform from the query:

SELECT contact.ID, contact.RespondentID, contact.FirstName, contact.LastName, FROM contact LEFT JOIN availability ON Availability = availability.ID
WHERE ((([availability].[When?]) Is Not Null And ([availability].[When?])<>'NOT AVAILABLE AT THESE TIMES'));

Basically the [When?] prompts the user to enter a date value. They can type in "MON AUG 13" or "TUES AUG 14" and so on.

What I wanted to do is to link the combo box to the subform so instead of having the user enter the date on a box that pops up when you open the form, you get to use the combo box to fill in that field.

I hope this makes sense. Thanks.

 

[dhookom]

I find it hard to believe your subform query sql will work without a decent join expression. Did you copy and paste this into your reply? Am I missing something?

Change the Name of the combo box to "cboDateField" and change the name of the subform control to "sfrmResults".

Change the combo box RowSourceType of cboDateField to "Field List" and set its Row Source to Availability. This should display the field names from availability in the drop down.

Then in the after update event of the cboDateField, use code like:

   Dim strSQL as String
   strSQL = "SELECT contact.ID, RespondentID, FirstName, LastName " & _
   "FROM contact LEFT JOIN availability ON Contact.ID = Availability.ID " & _
   "WHERE [" & Me.cboDateField & "] = '" & Me.cboDateField & "';"
   Me.sfrmResults.Form.RecordSource = strSQL

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]

 

[lck092580]

Hi Duane,

When you say change the subform control to "strmResults" where do I find this? I'm getting a "strmResults Member not found so I suspect I'm not putting this in the right place.

Thanks.

 

[dhookom]

Since you hadn't provided the name of your subform control, I suggested you [blue]change the name of the subform control to "sfrmResults"[/blue]. Every control on your form has a name property. I expected you to find the Name property of the subform control on your main form and change it.

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]

 

[lck092580]

Hi Duance,

I think I get it now after reading it a few times.

The code you wrote with: Me.sfrmResults.Form.RecordSource = strSQL is to inject that strSQL into the Record Source property of the FORM sfrmResults right? Sorry for being dumb about that.

I've already renamed that subform to sfrmResults but everytime I grab anything in the combo box, I get a:

Compile error:

Method or Data member not found

Sounds like it's not finding the subform I have at the bottom. Any tips on where to check?

Thanks and I *really* appreciate your continuing help.

 

[dhookom]

You should have some indication of which line is causing the error. Have you attempted to Compile your code?

Also, go to the After Update event of the combo box and type in:
Me.sfr
You should see a drop down of your subform name if it is named correctly.

How about replying with all your code in the form's module?

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]

 

[lck092580]

Hi Duance,

The combo box's after update event already has the original code you gave me:

Dim strSQL as String
strSQL = "SELECT contact.ID, RespondentID, FirstName, LastName " & _
"FROM contact LEFT JOIN availability ON Contact.ID = Availability.ID " & _
"WHERE [" & Me.cboDateField & "] = '" & Me.cboDateField & "';"
Me.sfrmResults.Form.RecordSource = strSQL

.. as an event procedure.

The debugger highlights ".sfrmResults" which is why I suspect that it's not finding that object to apply the record source in.

In terms of compiling, sorry about the stupidity but this needs to be manually compiled?

Basically what I did was go into the the After Update tag, hit the "..." button, chose code building, then entered the code you had given me. Currently it looks like this:

Private Sub cboDateField_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT contact.ID, RespondentID, FirstName, LastName " & _
"FROM contact LEFT JOIN Conference_Availability ON Contact.ID = Conference_Availability.ID " & _
"WHERE [" & Me.cboDateField & "] = '" & Me.cboDateField & "';"
Me.sfrmResults.Form.RecordSource = strSQL
End Sub

Then I closed the window.

I apologize for my lack of knowledge with VB. I did java back in the days and never really got into VB or C++.

 

[dhookom]

You don't have to know anything about coding to find the Name property of the subform control. Open your main form in design view. Single-Click the subform to select it. If the Properties dialog is not open, select View->Properties.

There is a Name property and the same value will appear in the title of the Property box [blue][tt]Subform/Subreport:[/tt][/blue] followed by the actual name that should be "sfrmResults".

To test your code by compiling, check this link

http://www.hookom.net/access/Articles.htm

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]