comb boxes on a form

[bigdog92]

hello,

i have a quick question hopefully one of you guys can answer. i have a form and one of the combo boxes (which is set up to pull its rows from a table) doesnt put the information in a table. it has field1 and field2 (which its bound to). when you click on the box field1 has unique data but field 2 is basically a category field so it will not be unique. the problem is it will find the first item in the category say item1 and shows category1, but if i click item2 with category1 it gives me the first item with that category. any ideas?

 

[WildHare]

You probably shouldn't bind your combo to the non-unique field - when you do, Access just finds the FIRST instance of that data, which may not be the specific one you want.

If you want to leave the combo the way it is, you'll have to alter the code behind the combo to find the record with Field1 AND Field2 equal to the two columns in the combo.

Check the code behind the combo box, and refer to BOTH columns, something like

...[table-name]!Field1 = me!combo.column(0) AND [table-name]!Field2 = me!combo.column(1)

(as you note, combo columns are ZERO-index based, the first column is Column(0) and so on...

Hope this helps.


Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[bigdog92]

when you say the code behind the combo box do you mean the row source or the control source? i tried:
invoice!No = me!combo125.column(0) and invoice!category =me!combo125.column(1)
dont know if that would be correct or where to actually put it.

 

[WildHare]

Actually, I meant the code that fires on the combo boxe's AFTER UPDATE event - what happens when you actually CLICK an entry.

If the combo box wizard wrote your code, you'll see that it does a FINDFIRST (I think....) and a BOOKMARK to position to the matching record in the table. You need to just adjust that line of code in the FINDFIRST to look for BOTH columns in the combo box - with the caveat that you need to be careful about putting QUOTES around any TEXT type values, which it looks like your CATEGORY field might be:

Invoice!No= Me!Combo125.Column(0) and Invoice!Category = [red]'[/red] " & me!Combo125!column(1) & "[red]'[/red]"

Hope that's clear - if not, post back.

Jim


If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[bigdog92]

I entered it into the after update code and now i recieve: Procedure decleration does not match description of event or procedure having the same name.

Private Sub Combo151_BeforeUpdate(Cancel As Integer)
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "Invoice!No= Me!Combo151.Column(0) and Invoice!Category = ' " & Me!Combo151!Column(1) & "'"
Me.Bookmark = rs.Bookmark
End Sub

and an after update as:

Private Sub Combo151_AfterUpdate(Cancel As Integer)
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "Invoice!No= Me!Combo151.Column(0) and Invoice!Category = ' " & Me!Combo151!Column(1) & "'"
Me.Bookmark = rs.Bookmark
End Sub

it works but throws up that error right away. thanks for all your help so far. this is driving me nuts as you can tell lol.

 

[WildHare]

There appears to be a slight dichotomy between your statement, and the posted code:

I entered it into the after update code and now i recieve: Procedure decleration does not match description of event or procedure having the same name.

Private Sub Combo151_BeforeUpdate(Cancel As Integer)
----------------------------------------------

The AFTER_UPDATE event of a combo does not have a CANCEL option, so that might be the problem - make sure your AFTER_UPDATE event sub header looks like this:

Private Sub Combo151_AfterUpdate()
....


and DELETE any code block on the BEFORE_UPDATE event.

Jim



If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[bigdog92]

if i take the code block out of the before update i recieve this:

Property let procedure not defined and property get procedure did not return an object (Error 451)


Certain properties, methods, and operations can only apply to Collection objects. This error has the following cause and solution:

You specified an operation or property that is exclusive to collections, but the object isn't a collection.
Check the spelling of the object or property name, or verify that the object is a Collection object. Also look at the Add method used to add the object to the collection to be sure the syntax is correct and that any identifiers were spelled correctly.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

 

[bigdog92]

if someone else could help me out i would appreciate it, thanks.

 

[WildHare]

Sorry for the fumble, BD - can you either post the complete After_Update event code here,or if you'd prefer, zip and email a sample of the system to me - I can probably look at it sometime between now and this evening. My email addy is on the website at the bottom of my sig.

Your errors are rather odd - they don't sound like they're due to the combo box, but rather some line of code somewhere that is out flapping in the breeze where it shouldn't be.."Property LET" ??????

If there's not a lot of code in the module, you might even post the entire block - just make sure we can figure out what went where.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[bigdog92]

i gave up and just used a list box and the user said that was fine. thanks for all the help you did give me though.