combobox additem issue

[gogetsome]

Hello, I have been working at this for days and am no better off. I could really use some help.
I need to populate a combobox with data from a column in an SQL table. I have put together the code below but am having an error at this line:

invBegNum.additem rs.Fields.Item("RMDNUMWK").Value

Error message:

"Object doesn't support this property or method"

I'm really a newb but am trying hard to get this. Here is the code. Please help.

visual basic code

Sub getInvData()
Dim conn As ADODB.Connection
Dim connString As String

conString = "Provider=sqloledb;Data Source=localhost;Initial Catalog=BBTN;User Id=scott;Password=bigdaddy"

Set conn = New ADODB.Connection
conn.ConnectionString = connString
conn.Open connString

Dim rs As ADODB.RecordSet
Dim sSQL As String

Set rs = New ADODB.RecordSet
sSQL = "select RMDNUMWK from SV00564 order by RMDNUMWK"
rs.Open sSQL, conn

Do Until rs.EOF
invBegNum.additem rs.Fields.Item("RMDNUMWK").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

 

[HarleyQuinn]

Just working from memory here but I don't think you need the .Item part of the syntax.

Have you tried:

invBegNum.additem rs.Fields("RMDNUMWK").Value

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[gogetsome]

Thanks HarleyQuinn for your reply. I tried your suggesion and it gave the same error message.

When writing the line of code intellisense does not list .additem as an available method or property. Could this be the issue?

 

[rmikesmith]

Yes. Where is the combobox invBegNum? Userform, Worksheet? If worksheet, are you using the combo control from the Controls toolbar or Forms toolbar? The Forms version does not have an AddItem method.


Regards,
Mike

 

[gogetsome]

Thanks rmikesmith for joining in. The combobox was added to the an existing form from the toolbox in the vb editor. All the examples that I have researched up to this point on how to populate a combobox says use the .additem method. It makes sense that I would get an error if the forms version does not have this method available.
So am I back to square one? or is there another way to accomplish my task of populating a combobox with data from a DB?

 

[rmikesmith]

Sorry, there is some confusion: On a worksheet you can add a combobox from either the Controls toobar or the Forms toolbar. The latter should not be confused with the combobox control that is added to a Userform from the toobox in the VBE. A combobox control on a Userform does indeed have the AddItem method. Therefore that doesn't appear to be your problem.

I don't have any other suggestions at the moment.


Regards,
Mike

 

[rmikesmith]

Some questions:

Where is Sub getInvData() located?

invBegNum seems an odd name for a ComboBox (no offense). Is this the actual name given to the control on the Userform?

Mike

 

[gogetsome]

LOL, sorry for the newbness. invBegNum is the actual name of the control on the userform. It was assigned something else when it was dropped onto the form but I changed it to the name I'm using in a stored procedure for consistancy. The sub getinvdata() is on the form with the rest of the code.. I suppose that I could just put the code in the window_beforeactivate?

Just a shot but, does the combobox require an import statement to allow all it's methods?

here is the entire code from the dynamic objects code window for the form.

*********

'Display Crystal Report
Private Sub crystal_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

Dim objCmd As ADODB.Command
Dim sDataConnect As String
Dim lngErrorNum As Long

'connection OBJECT
Dim objConn As New ADODB.Connection
objConn.Open "Provider=sqloledb;Data Source=localhost;Initial Catalog=BBTN;User Id=scott;Password=bigdaddy"

'Command object
Set objCmd = CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = objConn
.CommandType = adCmdStoredProc
.CommandText = "cssp_Maintenance_Invoice"
.Parameters.Append .CreateParameter("@invBegNum", adVarChar, adParamInput, 30, 1)
.Parameters.Append .CreateParameter("@invEndNum", adVarChar, adParamInput, 30, 1)
.Execute
End With

Set objCmd = Nothing
objConn.Close
Set objConn = Nothing

'Render Report
Set s = CreateObject("InternetExplorer.application")
s.Visible = True
s.Navigate "

http://exch-server/scrreports/Service/Maintenance/maintenanceInvoice.rpt?&prompt0="

& invbegnum & "&prompt1=" & invEndNum
End Sub
ADODB.Connection

Private Sub Window_BeforeActivate()
Call getinvData
End Sub

Sub getinvData()

Dim conn As ADODB.Connection
Dim connString As String

Set conn = New ADODB.Connection
conn.ConnectionString = connString
conn.Open "Provider=sqloledb;Data Source=localhost;Initial Catalog=BBTN;User Id=scott;Password=bigdaddy"

Dim rs As ADODB.RecordSet
Dim sSQL As String

Set rs = New ADODB.RecordSet
sSQL = "select RMDNUMWK from SV00564 order by RMDNUMWK"
rs.Open sSQL, conn

Do Until rs.EOF
invbegnum.additem rs.Fields("RMDNUMWK").Value

rs.MoveNext
Loop
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub

 

[xlbo]

so this code is in Crystal reports ???

if so, you should really post this in a Crystal forum - this is VBA for MICROSOFT products - hence the forum title:
VBA Visual Basic for Applications (Microsoft) Forum

 

[gogetsome]

No, the code is in VBA 6.3 which comes with Great Plains. I'm just passing parameters to a stored proc and calling a crystal report. The comboboxes will be used to obtain the values from SQL that are needed to pass to the SPROC. So I believe that I'm in the correct forum.

The issue that I'm having is that VBA 6.3 (Great Plains version) does not appear to have the additem or rowsource methods available to the combobox or other similar type controls.

Even worse I cannot find an example of how to get around this. There must be another way to populate a combobox other than what I'm attempting?

 

[xlbo]

I guess you need to look at the object model and see what methods are available for a combobox in Great Plains then because from the sounds of it, it ain't the standard one in VBA

 

[fumei]

I am having difficulties. I am with xlbo on this one. A UserForm combobox in VBA DOES have .AddItem as a method. I have never heard of an example where it does not. I find it hard to accept that somehow (even though it is VBA 6.3) Great Plains VBA skipped a crucial - nay, essential - method for such a common object.

Something is not right here.

Out of interest, what reference do you have for MSForms?

Gerry

 

[xlbo]

mmmmmm - I can kind of believe it - the VBA shipped with Busines Objects is riddled with holes and omissions - nothing quite as big as this would be though....

 

[gogetsome]

Yes, it does seem crazy. Here are the methods and properties that intelesence shows:

caption
empty
enabled
height
left
locked
name
object
parent
required
tabstop
top
value
valueseg
visible
width

focus
ficusSeg
move

How do I find the reference for MSforms?

 

[fumei]

YIKES! That would be truly sloppy - even by Microsoft standards.

Shudder.....

Gerry

 

[rmikesmith]

Wow,

Most of those methods/properties are unrecognizable from a MSForms ComboBox perspective.

I think the only hope is to look into the Great Plains documentation on this, as xlbo suggested.


Regards,
Mike

 

[gogetsome]

Will do rmikesmith. Thanks for everyones help thus far.

 

[fumei]

To check references, in the VBE, Tools > References.

Gerry

 

[xlbo]

Seems like this is a persistent issue out there is google land !! not a lot of references but you are not the only one struggling with this - the only thing I can think of is to suggest that it may be similar to MS Access in that you may have to BIND the control to a filed in a table....

 

[ck1999]

have you tried

invBegNum.additem "hello"

This will let you know if it is an additem issue or the variable you are trying to add

Chris