?
Solved

Filling a combo box error

Posted on 2005-04-17
15
Medium Priority
?
149 Views
Last Modified: 2010-05-02
Hi I have the following code in a form load
   
   DatPaymentCodes.DatabaseName = "ShoeBox2.mdb"
   DatPaymentCodes.RecordSource = "SELECT DISTINCT [PaymentCode] FROM PaymentCodes"
   DatPaymentCodes.Refresh
 
  Do While Not DatPaymentCodes.Recordset.EOF
      vntTemp = DatPaymentCodes.Recordset![PaymentCode]
      If IsNull(vntTemp) Then vntTemp = ""
      CmbPaymentCodes.AddItem CStr(vntTemp)
      DatPaymentCodes.Recordset.MoveNext
   Loop

   DatCreditors.DatabaseName = "ShoeBox2.mdb"
   DatCreditors.RecordSource = "SELECT DISTINCT [Creditor Name] FROM Creditors"
   DatCreditors.Refresh

   Do While Not DatCreditors.Recordset.EOF
   On Error Resume Next
      vntTemp = DatCreditors.Recordset![Creditor Name]
      If IsNull(vntTemp) Then vntTemp = ""
      CmbInvoiceto.AddItem CStr(vntTemp)
      DatCreditors.Recordset.MoveNext
   Loop
The first combo box CmbPaymentCodes loads fine but the second CmbInvoiceto give the following error
"Object doesnt support this property or method" for every attempted entry into the combo box, ie if the the table datcreditors has 4 enrtys i get the error messege 4 times, after clicking ok 4 times the form loads with the combo box populated. I think i'm missing something obvious here but any help greatly appreciated.

Dave
0
Comment
Question by:davemckenna
  • 6
  • 4
  • 2
  • +2
15 Comments
 
LVL 28

Expert Comment

by:vinnyd79
ID: 13801153
If you take the on error resume next out does it cause a different error first?
Why are you using Cstr on vntTemp? Couldn't you define it as a string?
0
 

Author Comment

by:davemckenna
ID: 13801196
No, the same error appears regardless.

To be honest i got the code above from a different post, it has been working fine up to now with various data types. Do you mean drop the Cstr and define vntTemp (currently defined as a variant) as a string?  Tried that and the same problem occurs.
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13801268
  DatPaymentCodes.DatabaseName = "ShoeBox2.mdb"
   DatPaymentCodes.RecordSource = "SELECT DISTINCT [PaymentCode] FROM PaymentCodes"
   DatPaymentCodes.Refresh
 
  Do While Not DatPaymentCodes.Recordset.EOF
      vntTemp = DatPaymentCodes.Recordset![PaymentCode]
      If IsNull(vntTemp) Then vntTemp = ""
      CmbPaymentCodes.AddItem CStr(vntTemp)
      DatPaymentCodes.Recordset.MoveNext
   Loop

   DatCreditors.DatabaseName = "ShoeBox2.mdb"
   DatCreditors.RecordSource = "SELECT DISTINCT [Creditor Name] FROM Creditors"
   DatCreditors.Refresh

   Do While Not DatCreditors.Recordset.EOF
      vntTemp = DatCreditors.Recordset![Creditor Name]
      If IsNull(vntTemp) Then vntTemp = ""
      CmbInvoiceto.AddItem CStr(vntTemp)
      DatCreditors.Recordset.MoveNext
   Loop
Try that ??
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:davemckenna
ID: 13801555
Same error, what did you change?
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13801636
Just the on error resume next because you had that in there and I was just trying to make sure you had tried it as per vinnyd's suggestoin :) but it didnt work :)
0
 
LVL 28

Expert Comment

by:vinnyd79
ID: 13801692
Does it work if you use:

 vntTemp = DatCreditors.Recordset.Fields.("Creditor Name").Value
0
 

Expert Comment

by:PradnyaDeshmukh
ID: 13803806
I think the code u have written has no problems. I tried the same code & it is working fine even i removed Resume next statement. Just check another property settings related to combo box or recordset. Otherwise code has no problems.
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13804327
What about removing the 2nd combo box ie deleting it inserting a new combo box and giving it the same name ie CmbInvoiceto

That should reset all the properties to there default values for the combobox :)
0
 
LVL 1

Expert Comment

by:Gladiss
ID: 13804380
hi davemckenna
Can u please check whether the combo name is exactly

   CmbInvoiceto


0
 

Expert Comment

by:PradnyaDeshmukh
ID: 13804418
I think CmbInvoiceto combo name is correct. Thats why Dave is saying it gets populated after error messages.
0
 

Author Comment

by:davemckenna
ID: 13808612
Hi guys, yes i already tried removing and adding the combobox and the DatCreditors data control, but no joy!. I do agree however that this problems bangs of a property setting being out of place somewhere, i will have a closer look at other controls settings and revert back to you.

Thank you.
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13808646
what about doing this :

 DatPaymentCodes.DatabaseName = "ShoeBox2.mdb"
   DatPaymentCodes.RecordSource = "SELECT DISTINCT [PaymentCode] FROM PaymentCodes"
   DatPaymentCodes.Refresh
 
  Do While Not DatPaymentCodes.Recordset.EOF
      vntTemp = DatPaymentCodes.Recordset![PaymentCode]
      If IsNull(vntTemp) Then vntTemp = ""
      CmbPaymentCodes.AddItem CStr(vntTemp)
      DatPaymentCodes.Recordset.MoveNext
   Loop
On Error GoTo Prob
   DatCreditors.DatabaseName = "ShoeBox2.mdb"
   DatCreditors.RecordSource = "SELECT DISTINCT [Creditor Name] FROM Creditors"
   DatCreditors.Refresh

   Do While Not DatCreditors.Recordset.EOF
      vntTemp = DatCreditors.Recordset![Creditor Name]
      If IsNull(vntTemp) Then vntTemp = ""
      CmbInvoiceto.AddItem CStr(vntTemp)
      DatCreditors.Recordset.MoveNext
   Loop
Prob:
Resume Next
'MsgBox err.description '<-- that might give you some useful info :) if not then you can comment it out and it will just go through all the records, add them all and hopefully not give you the error messages :)
0
 
LVL 23

Accepted Solution

by:
gecko_au2003 earned 1500 total points
ID: 13808654
I know its not the perfect solution but it will get rid of those error messages :) unless you keep that MsgBox err.description :)
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 13831163
I thought you were going to wait for someone else's reply lol, thanks for the grade and points though ! I wasnt expecting that to be honest :)
0
 

Author Comment

by:davemckenna
ID: 13833491
To be honest, i'm finished my final year project this Friday, and my subscription is ending, so somebody might as well get em!

Cheers
Dave
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question