filter a form on load

[markdooler]

Hi All

Need some help again!

i have a form that displays all the users in my system called frmUserList (form created from the tblUsers)

I also have an edit user form called frmEditUsers

I have set the on click event on the fields in the frmUserList to open the frmEditUsers but cant seem to get it to filter to the correct record.

Below is the macro setup i have used in the past for another form with this funtion but i just cant get it to work

with it set like this the below comes up when you click it.

Im sure i have not given you enough info but any ideas?

 

[Ranman256]

numbers to not need quotes around the text box,
Strings (username) require (single) quotes: where [LastName]= '" & txtName & "'"

Note: using single quotes will fail if the name has a single quote in it like: O'brien, etc.
So you can use Dbl-quotes " ,but that will fail if that has a dbl-quote in it. (tho more rare for names)
You cant quote a quote.

I have a
public Constant Q = """"
then its: where [LastName]= " & Q & txtName & Q

 

[markdooler]

numbers to not need quotes around the text box,
Strings (username) require (single) quotes: where [LastName]= '" & txtName & "'"

Note: using single quotes will fail if the name has a single quote in it like: O'brien, etc.
So you can use Dbl-quotes " ,but that will fail if that has a dbl-quote in it. (tho more rare for names)
You cant quote a quote.

I have a
public Constant Q = """"
then its: where [LastName]= " & Q & txtName & Q

ok so have tried this

But still no joy.

So to be clear, i want the form (frmEditUser) to only display the record that is clicked on the form (frmUserList).

The txtUserName is the same on both forms.

I have done this sucessfully with numbers bit cant seem to do it using text!

 

[bastanu]

Use VBA instead:

DoCmd.OpenForm "frmEditUsers", , ,"[LastName] = '" & Forms![frmUserList]![txtUserName & "'"   'note that [LastName] is the name of the field in the form's record source

Cheers,
Vlad

 

[Pat Hartman]

Use the VBA solution posted by Vlad but enclose the name with double quotes since single quotes are too common to ignore.

 

[markdooler]

Use VBA instead:

DoCmd.OpenForm "frmEditUsers", , ,"[LastName] = '" & Forms![frmUserList]![txtUserName & "'"   'note that [LastName] is the name of the field in the form's record source

Cheers,
Vlad

The code

The error

Any ideas?

 

[Pat Hartman]

I guess you didn't read my post #5. Also, LastName is unlikely to be unique so who knows what record you would end up on. The error may be occuring because you are using a lookup field on your table and the data value isn't what you think it is.

 

[markdooler]

I guess you didn't read my post #5. Also, LastName is unlikely to be unique so who knows what record you would end up on. The error may be occuring because you are using a lookup field on your table and the data value isn't what you think it is.

Sorry, i did i just wanted to get it working before i played with the syntax.

I have already changed LastName to UserName as this is unique to each user.

Still cant work out why it doesn't work tho.

 

[Pat Hartman]

Do you have lookups defined on the table? They will interfere with what you are trying to do.

PS. Do it right the first time. You don't want to have to go back once something is "working" looking for code you know for a fact will cause errors.

 

[markdooler]

Do you have lookups defined on the table? They will interfere with what you are trying to do.

PS. Do it right the first time. You don't want to have to go back once something is "working" looking for code you know for a fact will cause errors.

Very true

The frmUserList is a form based on qryCusrrentSiteUsers that filters the tblUsers using a tempvar for the site that the user is logged in as.

I have a similar set of queries that are first filtered by the site, then by the user name, then a click to update function based on a number and this works fine.

As i see it im trying to do the same but around text rather than numbers.

Hopefully that makes sense.