Set object with string

[James Hallam]

This should be really easy but I am at a loss!

What I have...
8 comboboxes labelled Company1, Company2 etc thru to Company8

I have a piece of code which will fill each combobox with some data
from an excel spreadsheet, this works fine. The code is repeated 8
times, for each combobox.

The working code:

With company
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
UserForm1.Company1.ColumnCount = comp.Fields.Count
UserForm1.Company1.Column = comp.GetRows(NoOfRecords)

What I would like to do is to change the code so I can loop it round 8
times, once for each combobox. this is the code I have tried, but I
won't work...

Dim i As Integer
Dim sref As String
Dim ref As Object
Dim ref2 As Object

For i = 1 To 8
With comp
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

sref = "UserForm1.Company" & i
Set ref = sref
ref.ColumnCount = comp.Fields.Count

Set ref2 = "UserForm1.Company" & i
ref2.Column = comp.GetRows(NoOfRecords)
Next i

Neither ref or ref2 work.

I came to the conclusion that I would need use a pointer (which I
assume the set command is used for in VBA). Any ideas why I can't set
a point with a string?

Any help would be appreiated

 

[Jonathan West]

Hi James,

This is what you need

Set ref = UserForm1.Controls("Company" & Cstr(i))

If the code is running inside the userform, you should use Me instead of
UserForm1. The me keyword refers to the current instance of the form, which
is usually the one you want to be working on.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[James Hallam]

Cheers, I got it working.
The code is in a module, hence using userform1 and not me.

I actually had to use the CStr function for "company" as well (there
were a number of different prefixs. This made me wonder why the CStr
function is used - surely the variable I use to store the string
"company" is already a string and doesn't need converting?

I have a working program, just a little confused as when to use CStr in
the future...

The final code is below...

***Definitions have not been pasted in

For j = 1 To 4
Select Case (j)
Case 1
name = "Company"
Set ref = data.OpenRecordset("SELECT * FROM `company`")
Case 2
name = "People"
Set ref = data.OpenRecordset("SELECT * FROM `people`")
Case 3
name = "Phone"
Set ref = data.OpenRecordset("SELECT * FROM `phone`")
Case 4
name = "Pos"
Set ref = data.OpenRecordset("SELECT * FROM `position`")
End Select
For i = 1 To 8
With ref
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
Set colcnt = UserForm1.Controls(CStr(name) & CStr(i))
colcnt.ColumnCount = ref.Fields.Count
Set colnum = UserForm1.Controls(CStr(name) & CStr(i))
colnum.Column = ref.GetRows(NoOfRecords)
Next i
Next j

 

[Jonathan West]

Cheers, I got it working.
The code is in a module, hence using userform1 and not me.

I actually had to use the CStr function for "company" as well (there
were a number of different prefixs. This made me wonder why the CStr
function is used - surely the variable I use to store the string
"company" is already a string and doesn't need converting?

I have a working program, just a little confused as when to use CStr in
the future...

The final code is below...

***Definitions have not been pasted in

It is the definitions I would need to see in order to work out why yo needed
CStr - what definition have ou given to the "name" variable.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[James Hallam]

Dim data, ref, prot As DAO.Database
Dim i As Integer, j As Integer
Dim colcnt As Object, colnum As Object
Dim NoOfRecords As Long
Dim MyPath, name As String

 

[Jonathan West]

Dim data, ref, prot As DAO.Database
Dim i As Integer, j As Integer
Dim colcnt As Object, colnum As Object
Dim NoOfRecords As Long
Dim MyPath, name As String

Do you realise that you have declared data, ref and MyPath all as Variant?

I would be very wary of giving any variable the name "name". There re too
many Name properties around the place for it to get mixed up with.

That said, there's no obvious reason why you had to use CStr. It would seem
that you were converting string data into string data.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org