I'm still stuck on this combo box

[expublish]

I have a combo box (called Combo14) on a form. It is looking up a field (employee name - employee id is the p/k but i don't want that listed - just employee name) in a table (employee) and when the user selects a name it takes them to the relevant record. Now, the values are listed OK, but whatever name I click that is listed it always takes me to the first record, not the relevant record that was selected.

Anyone got any ideas? The code looks like this:

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click


DoCmd.Close

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub
Private Sub Combo14_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Employee ID] = " & Str(Me![Combo14])
Me.Bookmark = rs.Bookmark
End Sub

Private Sub searchemp_BeforeUpdate(Cancel As Integer)

End Sub

Can anyone shed some light and ammend the above code to get me out of this very deep hole that seems to be getting deeper and deeper ... hello? heeeeeellooooooooo?

Ta. Scott.

[This message has been edited by expublish (edited 03-06-2002).]

 

[Rich]

Shouldn't it be "[Employee ID] = " & Me.Combo14

 

[expublish]

OK, did that and now the selections are not responding at all. Just to confirm, the event AfterUpdate in the properties box on the form it set to [Event Procedure] - that is right isn't it?

The code now looks like this(last time I c&p too much):

Private Sub Combo14_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Employee ID] = " & Me.Combo14
Me.Bookmark = rs.Bookmark
End Sub

Anymore ideas people?

Thanks.

[This message has been edited by expublish (edited 03-06-2002).]

 

[utdel]

try adding this to your code:

rs.FindFirst "[Employee ID] = " Str(Nz(Me![Combo14], 0))

then on the column width for the combo box set them to 0";1" 0=no width for key so it won't display then adjust the employee name.

and if nothing else use the combo wizard to readd the combo box. it should set up the combo box like you want it.

[This message has been edited by utdel (edited 03-06-2002).]

 

[Hayley Baxter]

My form works perfectly doing the same thing I have compared your code to mine and it looks identical apart from the name differences that is. Did you use the wizard to set this combo up? I could be wrong but by reading through your post it sounds as though the combo is looking up your employee table(option 1 from the wizard) rather than option 3 from the wizard which will change a record to match whatever you select from your form.

Hayley

 

[expublish]

Didn't have any luck with code. It brought up a compile erorr: syntax error. So I started again using the wizard doing the following:

Find a record on my form based on a value I selected in my combo box >

Selected Employee Name as the field I want it to look up >

Adjusted width of column to make it big enough. Selected 'Hide Key Column'. >

Named my combo Employee Name.

Now I go to use the combo in form view and I am back to sq 1. The vales are there but when I select one it doesn't go anywhere.

This is doing my head in! Is there supposed to be a control source selected?

My 'new' code looks like this:

Private Sub Combo23_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Employee ID] = " & Str(Me![Combo23])
Me.Bookmark = rs.Bookmark
End Sub

PLEASE HELP ME !!!!!!!!!!

[This message has been edited by expublish (edited 03-06-2002).]

 

[Hayley Baxter]

You said it was looking up your employee name. It should be looking up the employeeID. Start the wizard again take the fields employeeID and employee name, hide key column = yes. Now this will mean your bound column is 1 so it will look for your id no but you will get the list in column 2 employee name. Try it again and let us know if you've had any luck.

One more thing you are looking up a record on your form so is the employeeID no actually on your form??

Hayley

[This message has been edited by Hayley Baxter (edited 03-06-2002).]

 

[expublish]

Hayley,

Did what you said but it has made no difference. Still same problem. Could you c&p your code so I can compare. Also, any more ideas?

Cheers
Scott

 

[Hayley Baxter]

Scott

I cannot understand why that is happening if you have followed all of the above but here is my code:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Me![Combo22])
Me.Bookmark = rs.Bookmark
End Sub

What are you choosing after this in the wizard? store the value for later use. Your combo box is set to unbound?

Hayley



[This message has been edited by Hayley Baxter (edited 03-06-2002).]

 

[expublish]

I have copied your code, changing names, no luck.

Last through of the dice was to start a completely new form, with Just this combo on and nothing else to interfere.

IT DIDN'T WORK!

Before I curl and cry like a baby who has lost his candy, please can you confirm that I have the following correct (I know the code is right):

1) Control Source = nothing
2) Row Source Type = table/query
3) Row Source = SELECT [Employees].[Employee ID], [Employees].[Employee Name] FROM Employees;
4)Event: After Update = [Event Procedure]

Is this all right?
Is there anything else that I am missing?

My combo is set to unbound. I didn't get the option to store it for later use.

Does anyone else have ANY idea what is going on. I cannot for the hell of me work it out.

Cheers
Scott

[This message has been edited by expublish (edited 03-06-2002).]

 

[Hayley Baxter]

Everything is right Scott what exactly is on your old form? In my form I have suppliers combo box is suppID and supp name now when I select a supp name from the combo it changes the supp record as well as my subfrm details. As lonh as you have the values you are searching for on your form everything else is right. If that's not working for you I'm afraid that's got me stumped.

The wizard should sort this for you anyway but just check in your properties that bound column is 1 and column count is 2.

Anyone else???

[This message has been edited by Hayley Baxter (edited 03-06-2002).]

 

[expublish]

I have reverted back to my old form now seeing as the new one didn't get me any further.

My old one is like a menu page. It has this (cursed) combo then has 5 command buttons below. The command buttons all have on Click events that run macro's to open other forms.

I can't see why they would interfer.

The Record Source in the properties of the whole form is set to Employees (which is the correct table that has Employee Name and Employee ID in it).

Does any of that make a difference?

Scott
(creator of mystic threads that no-one can solve)

 

[expublish]

I have exported the relevant form and table into a new database file and zipped this up. Hayley, can I email this to you for you to look at?

If so, pls send me your email address if you don't want to post it. Use:

margaretkombakono@hdvfoundation.org.uk

rather than the one I have on my profile. That is personal this is business.

BOUND is column 1 and COLUMN COUNT is 2.

Cheers
Scott.

PS - anyone else wanna gander?

[This message has been edited by expublish (edited 03-06-2002).]

 

[Hayley Baxter]

You can send it to me now if you like. I sent an email to your preferred choice.

Hayley

 

[expublish]

A new morning and new outlook on this *£@%$^& combo box.

Thats what I thought until I opened the database and realised that I am not really getting anywhere further than yesterday!

Hayley did you have any luck with the sample I sent you?

Anyone else looking at this thread with any ideas at all?

Cheers
Scott

 

[Hayley Baxter]

I have sent the sample back to you. It appears that you want to use the combo box to select an employee(your main switchboard) then click a command button to open the selected record, yes? That is not what I thought you were trying to do I was thinking you had a search form like my own ie select your employee and display the results of the selected employee on the same form. Would I be correct in saying this? I included your employee id and name on your main switchboard it appears your combo is working.

Hayley

 

[expublish]

Your are almost right. I do want to select a record from the combo, but there is no command button. The results are displayed on another form.

Well, that is the theory anyway. I have started a new thread clearly explaining what the problem is. It can be found at:
http://www.access-programmers.co.uk/ubb/Forum4/HTML/006036.html