dbl click to open current user in user table

[eburgtech]

Greetings.

I have an Assets database and I have an Employee field that is a combo box. The combo box has a row source gathering the first name and last name from the Contacts table. I found code to make it so that if I dbl click on the Employee field on my Asset form, that it opens the Contacts table, however I want it to open the record for the currently displayed Contact. Any help would be appreciated.

Thanks!

 

[HiTechCoach]

Greetings.

I have an Assets database and I have an Employee field that is a combo box. The combo box has a row source gathering the first name and last name from the Contacts table. I found code to make it so that if I dbl click on the Employee field on my Asset form, that it opens the Contacts table, however I want it to open the record for the currently displayed Contact. Any help would be appreciated.

Thanks!

To learn how to write the VBA code, use teh command button wizard to add a command button to open a form and view a specific record. Look at the vent procedure to see how to use the "Where condition"

DoCmd.OpenForm "FormName", , , "where condition"

example:

DoCmd.OpenForm "FormName", , , "ID =" Me.cboID

 

[eburgtech]

Thanks Coach. I understand Where clauses for the most part. But I don't see where the "where" clause is in the code.

Here's the code I'm using:
DoCmd.OpenForm "Users", acNormal, , , , , Me.User

I'm not trying to use a command button. I'm using a combo box that has the User name associated with the current Asset. I want to be able to double click the user when I am viewing a particular asset and have it open to that record on the Contacts table. I think the problem is that my User field on my Asset table is a combination of the First Name & Last Name fields from the Contacts table.
Here's the Row Source I am referring to:
SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] AS Expr1 FROM Contacts ORDER BY Contacts.[Last Name];

So I think that's why it can't match to the current record. Any ideas?

 

[HiTechCoach]

Thanks Coach. I understand Where clauses for the most part. But I don't see where the "where" clause is in the code.

Here's the code I'm using:
DoCmd.OpenForm "Users", acNormal, , , , , Me.User

from the Access help file:

OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

So you have:

DoCmd.OpenForm "Users", acNormal, , , , , Me.User

In your code you as passing an open args parameter,, but not a where parameter.

DoCmd.OpenForm "Users", acNormal, ,"ID = " Me.cbdControlName , , , Me.User

if the Open Arg is supposed to be the "where" then try:

DoCmd.OpenForm "Users", acNormal, ,"Contact.ID = " Me.User

I'm not trying to use a command button. I'm using a combo box ...

The code will basically be the same for the On click event of a command button or the double click event of a combo box. That is why I suggest using the command button wizard to write the VBA code form you. Then just cut and paste!

 

[HiTechCoach]

I think the problem is that my User field on my Asset table is a combination of the First Name & Last Name fields from the Contacts table.

That is a real design flaw and will definitely cause integrity issues. You would be storing the Primary key from the Contacts table as the foreign key value. This way you can look up the name as needed.

 

[eburgtech]

I'm trying to fix this design flaw. I have broken the Employee field up into FirstName and LastName fields in both the Users Table and the Assets Table (and associated forms and queries of course). So, now I have the Assets.FirstName & Assets.LastName fields populated by a query from the Users.FirstName & Users.LastName fields separately. This is all working fine now however...I have a search form I made which works great but is displaying both the FirstName and LastName fields (pulled from a query of the Assets Table) as numbers instead of the actual names. How do I get the Names that are being populated into the Assets.FirstName & Assets.LastName to display as the Names instead of the ID of the field?

 

[eburgtech]

To clarify, if I run the query itself, the data displays correctly. When the data displays in the unbound object (list box) it shows the First Name and Last Name fields as numbers. Why is it showing numbers in the list box but not in the original query? Any help would be greatly appreciated.

 

[boblarson]

To clarify, if I run the query itself, the data displays correctly. When the data displays in the unbound object (list box) it shows the First Name and Last Name fields as numbers. Why is it showing numbers in the list box but not in the original query? Any help would be greatly appreciated.

Sounds like you have some lookups at table level going on and should remove them.

 

[eburgtech]

lol You are correct sir. I have the following for First Name in the Assets Table: SELECT Users.ID, Users.[First Name] FROM Users ORDER BY Users.[First Name];

I have this because I made a drop down on my Assets form to tie each asset to a particular user. This was the only way I could think of to make that combo box work. I know you can make your own list of values on the combo box, but that wouldnt' tie the Assets to the Users table. Of course, I'm self-taught and I'm sure there's a better way but this has been working stellar until now. How would I tie an asset to a specific user using the FirstName & LastName fields without putting a query in those fields in the Assets Table? I'm totally for making things work the way they're supposed to. )

 

[boblarson]

You can use combos to pull data together on forms but don't use them directly in the tables. Remove them from the tables and then you use the query for the row source of the combo to be able to bring the names together for display while storing the id's.

 

[eburgtech]

Awesome. That worked beautifully and now my database is more normalized. Thanks! I love it when stuff works.