Populating Combo Boxes [on event]

[RoseV]

I have 3 combo boxes that are linked together. When anyone of them is changed or populated, I want Access to automatically populate the other 2 fields. I've tried ALL the options; OnChange, OnDirty, OnUpdate, AfterUpdate...all of them. The event procedure is in a Private Sub and what I'm doing is checking the value the user selected from the combo box and determining what the other two are. There is similar code in each event procedure. No matter what I do, I keep getting an error when I populate or change the value in any of the boxes - the error about not being able to evaluate the function or macro; The Expression AfterUpdate (or whatever I try)...produced the following error: Procedure Declaration does not match description of event or procedure having the same name. Blah, blah, blah. What am I missing here??

Private Sub CboCS_AfterUpdate()
(RS and all variables declared publicly)
Set RS = CurrentDb.OpenRecordset("SELECT TblItems.CS, TblItems.UPC, TblItems.Kras FROM TblItems GROUP BY TblItems.CS, TblItems.UPC, TblItems.Kras ORDER BY TblItems.CS;&quot
RS.MoveFirst
CCode = Me!CboCS.Value

Do Until RS!CS = CCode
If Not RS.EOF Then
RS.MoveNext
Else
Me!CboUPC.Value = RS!UPC
Me!CboKras.Value = RS!Kras
End If
Loop

'check if an AdName was selected
If Forms!frmorders!TxtAdName.Value <> &quot;N/A&quot; Then
'ad name was selected - get sale price based on CS
Call GetAdPricesCS
End If

End Sub


This was suggested:

RoseV

You want to have the code in that combo box's AfterUpdate property. The code would be something like, where &quot;Me&quot; = [name of 2nd CBO]:

Me.[name of 2nd CBO field].SetFocus
Me.[name of 2nd CBO field] = DLookup(etc., based on value of CBO1).

Hope this helps


*But it did not help. I get the same message. Do I need to set an additional reference in the modules? I can't figure this out. Anybody have any other ideas??

 

[GKProgrammer]

Hi Rose,

I have seen this error before but usually when the procedure is named incorrectly for example a beforeupdate event is usually like this:

Private Sub CboCS_BeforeUpdate(Cancel As Integer)

If you were to remove the Cancel from within the parenthesis then you would receive the error you are indicating above, like this:

Private Sub CboCS_BeforeUpdate()


It seems that your AfterUpdate pocedure is named OK though, which is kind of curious.

If I were you I would try to remove small chunks of the code at a time, that is within the offending procedure. This way you should be able narrow down exactly what is causing this problem. Let me know how it goes.



Regards,
gkprogrammer

 

[Tazcat]

Is your code resident in the form module, or are you running from a standard module? I ask because I noticed you are using the CurrentDb object. I had a similar error when using Currentdb and CurrentProject objects in a standard module in 2000. I had to pass those objects from the form module over to the standard module where the code executed to stop the error message. My variables were also publicly declared, but it still errored on me until I changed it.

I've also used the _Click() event to do the type of action you are describing, and find it works great.

 

[GKProgrammer]

Hi Again Rose,

I just remembered that this error message can also occur like this if another procedure has the wrong naming convention other than the control you are currently using and it will appear with this error message as if it has occured for this control when the error is actually due to a different one. Look through the naming conventions of each procedure you have for each control on the form, I am willing to bet that one of the predefined procedures is named incorrectly like the example that I supplied above with the BeforeUpdate procedure.

Regards,
gkprogrammer

 

[RoseV]

I had the cancel as integer in there previously, but it didn't work. Tried again, to no avail. As I am testing this procedure out, I only have the one sub defined behind the form. I also tried Tazcat suggestion - called a function in a stand-alone module, and passed the values, but it generated the same error.
Did I mention how much I hate Access...?
: )
Thanks for your help. Guess I'll keep working on it. I, of course, welcome any other suggestions you may have.

 

[GTLoco99]

Set RS = CurrentDb.OpenRecordset

try typing CurrentDb() or do this

Dim Db As CurrentDb()

Set RS = db.OpenRecordset

not sure if that will help but CurrentDb i dont think is going to work, need the &quot;()&quot; after it

GTLoco

 

[DaltonVB]

When I want to change the RowSource of a combo box, I use this methode.

Me!CboUPC.RowSource = "SELECT UPC FROM TblItems " _
                    & &quot;WHERE CS < &quot; & cboCS.Value & &quot;';&quot;
CboUPC.Requery

Me!CboKras.RowSource = &quot;SELECT Kras FROM TblItems &quot; _
                    & &quot;WHERE CS < &quot; & cboCS.Value & &quot;';&quot;
CboKras.Requery

The query has a criteria of WHERE CS < cboCS.Value because your logic does that.

You have CS ordered Ascending and you stop doing your loop when CS = cboCS.Value.

Hope this helps.

Thanks,
Dalton