Double-Click on Combo Box will Open Another Form at THAT record. 1

[PureCoffee]

I am trying to write a piece of Code so that when I double click on the Name of a Company on one form it will open that Record set in the "Companies" form.
The Form "Contacts" has a Combo Box which would select the Company the contact works for. This is set up through a "select query".

Does this make sense? Can anyone help me?
steve

 

[jebry]

Hi!

Try this In the combo box's DoubleClick event:

DoCmd.OpenForm "YourForm'sName", , , "CompanyName = '" & ComboBoxName.Text & "'"

This should open the form to the record that matches what is in the combo box.

hth
Jeff Bridgham
P.S. Please note that I used the .Text. .Value or just leaving it blank may not work in this case because the combo box will not update until it loses focus.

 

[PureCoffee]

Jeff,
I did that but get a Syntax error. Any other ideas?
Here is what I typed:

Private Sub CompanyID_DblClick(Cancel As Integer)
DoCmd.OpenForm "Companies", , , "CompanyName='"& CompanyID.Text&"'"
End Sub

 

[jebry]

Hi!

Assuming that you made no typing errors, then you need to put spaces on either side of both of your ampersands. If that is actually correct in your database then, is companyid a text or is it numerical. What I gave you assumed it was text. If it isn't, then remove the single quotes.

hth
Jeff Bridgham

 

[PureCoffee]

Jeff, IT WORKED!!

Now, is there a way to code more so that once I "double-click" and the "Company" form opens that the other(Contact) is closed down automatically?

Thank you.

Steve

 

[jebry]

Hi!

Yes there is, try DoCmd.Close acForm "Contacts".

hth
Jeff Bridgham
P.S. Glad it worked for you!

 

[PureCoffee]

Jeff,
I tried this but get a "Syntax" error. Most of the time it has to do with the program stating something along the lines of 'end of statement'. At any rate the "DoCmd.Open" portion works fine.

As a new Access User/Developer etc. what books would you recommend so I can learn this "code"? I can see that I will have much use for it in all of our dbases (Built and yet to be built).

I appreciate your time...

Thanks -Steve

 

[JoeMiller]

Didn't I already answer this? If you're still having issues I can send you your own database back working.
Joe Miller
joe.miller@flotech.net

 

[PureCoffee]

Joe,
Sorry! You did but it still didn't work. Not your fault I assure you! I felt I was taking to much of your time and so thought I would try the same question under "Forms Forum".

I finally got it working to open the Exact record (Name of Company in the field) and not just the "Companies" form. However I can't seem to get the DoCmd.Close thing to work meaning once I open "Companies" I want to Close Contacts so it is not in the background.

I am not a programmer, I am fairly new to access (Have taken College Course on Access 97 I & II but don't know anything about SQL Language, VBA or VB. Don't know the difference either.

So I am wanting very much to learn all I can, there is no one else in the company that knows as much or more than I do and the Chairman wants me to build various databases so we can keep track of data etc.

Any help would be appreciated but I don't want to be a PAIN. Please forgive me if I offended you. That was not my intention! Honestly!
I would love to see the code work as you have it. Also, any suggestion on Primers, books, etc as it relates to Access and the language it uses for expressions etc, would be appreciated. Thank you very much. Steve (sfraser@tay.com)

 

[jebry]

Hey Steve!

Try DoCmd.Close acForm, Me.Name

hth
Jeff Bridgham
P.S. If I were you, which I was about a year or so ago, I would start with the Help files. Of course the premiere book is The Access Developer's Handbook, but I don't know that it is a good one for beginners.