Drop Down Query

[xPaul]

All,

I have been getting quite frustrated with this to be honest with you.

What I have at the minute:

1. Query:

It manipulates my data in two ways: with a [Please enter your FIRST and LAST name:] criteria, and with records with a date less than 1 month from today

2. Form:
The form runs off of the above query, it uses a text box / combo box for each part of data from the query.

The issue:
The issue is that I cannot filter further in the query as it is producing a very small set of data.

There is also an issue when the user types in their name that isn't on the query and is presented with a blank screen and no safe way to exit the GUI.

What I trying to achieve:
I am trying to achieve a form where the user can select their name from a drop down rather than having to type it in.

____

I have followed guides on the internet to no success. They have left me more confused than anything else. Any help would be appreciated.

Thanks

 

[CJ_London]

Here is a suggestion. It assumes that your query has an ID column which has a unique value for each row (like employeeID)

1. Remove your prompted criteria in your query
2. Add an unbound combobox to your form - we'll call it CBEmployeeID for the recordsource of the combobox have something like "SELECT EmployeeID, LastName, Firstname FROM YourTable ORDER BY LastName, FirstName
3. ensure the bound column of the combo box is set to 1
4. ensure the number of columns is set to 3
5. ensure the column widths are set to 0;2;2 - you can adjust these later as required
6. ensure the limit to list property is set to yes
7. in the afterupdate event of the combobox put the following code

me.filter="[EmployeeID] = " & CBEmployeeID
me.filteron=true

Now, when you select a name from the combobox the form will be populated with that employee's details

 

[xPaul]

Okay, I have been able to complete what I had set out to do.

In the query I added a new column with the following:

[Name]=[Forms]![frm_log]![cboComboName] Or [Forms]![frm_log]![cboComboName] Is Null

Criteria = True

Then on my form I had an unbound combo box, that had a row source of my list of names.

On the On Click event I had Me.Requery.

Works!

___

However, I am still left with the annoying issue where by when a user selects their name, but it is not in the query the form blanks out.

I am trying to use an If Statement to check if there are previous records:

If DCount("[cboComboName]", "tbl_log", "[Name]= '" & Me.cboComboName & "'") = 0 Then
    Cancel = True
    Call MsgBox("Your name isn't held on this table!", vbExclamation, "Warning!")
Else
Me.Requery
End If

But this does not like users with an apostrophe in their name like O'Hagan etc.

Could I have a little help on that please?

 

[CJ_London]

If you have apostrophies in the name then you need to use replace to replace it with two single apostrophies - fairly standard practice where they are likely

"[Name]= '" & Replace(Me.cboComboName ,"'","''") & "'"

Alternatively you can use your query as the source for your combobox if this is appropriate - and you use the afterupdate event as I proposed

 

[xPaul]

That worked 100% CJ_London!