What do I need to do to make VB6 from work in Access

[Bruce Morin]

I created a form in VB6 that has a control array. I want to be able to
use this form in my access database. I will also need to reference a
combobox in my access database. What do I need to do? I am not a VB6
programming expert although I do OK in VBA within access (OK I'll come
clean - the control array was actually created by my 15 y/o in about 4
seconds when he saw me working on my problem in access - guess it speaks
highly of his HS VB class) Thanks a bunch for any suggestions offered.

 

[Douglas J. Steele]

I'm not quite sure what you're asking for. You can't "use" a form from
Visual Basic in Access. Your Access program could interact with your VB
program, but that requires a fair bit of work.

While Access doesn't have Control Arrays, you can simulate them. For
example, if you want 10 text boxes, name them txtBox1, txtBox2 and so on up
to txtBox10. The following code, in the Load event of your form, will then
put "Box 1", "Box 2" and so on into them:

Dim intLoop As Integer

For intLoop = 1 To 10
Me.Controls("txtBox" & intLoop) = "Box " & intLoop
Next intLoop

And how do you need to reference the combobox? Typically, it would simply be
Me!<name of combo box>

 

[Albert D. Kallal]

No..you can't use VB forms in ms-access

And, we don't have control arrays in ms-access.

It is one feature that we could use..but then again..we have a lot of
workarounds.

For example, in place of the nasty data repeater control, or control arrays
to repeat data on a screen, we can use continues form.

Take a look at the following screen shots to see what I mean:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

The main problem with VB forms is they are much simpler then ms-access
forms, and have WAY less features. This does mean that ms-access forms are
much harder to master then VB forms (there is a much larger learning curve
to using ms-access forms)......but the result is that we enjoy a good 3 to 1
in terms of productivity. (so, a $8000 project in ms-access will cost
$25,000 in VB).

There are number of good workarounds for control arrays...but which
workaround to use is going to depend on what you are trying to accomplish.

You can also write your applction in VB..and use the mdb files if you
want...

 

[Bruce Morin]

well, what I have is a array of checkboxes that the user can check to
concatonate items to a combobox rowsource. The number of checkboxes makes using
if..else statements cumbersome. This is the control array that will not work in
Access but works beautifully on the VB form that I cannot use:

Private Sub Command1_Click()
cbo.Clear
Dim strX(0 To 8) As String
Dim looper As Integer
For looper = 0 To 8
If checkbox(looper).Value = 1 Then
cbo.AddItem checkbox(looper).Caption
End If
Next looper
End Sub

Any suggestions for how to handle this with a minimum amount of code in VBA?

Thanks to you and Albert for your input

 

[Douglas J. Steele]

Unfortunately, there are differences between checkboxes in VB and in Access.
In Access, the checkbox doesn't have a caption: you have a separate label
associated with the control. Also, depending on what version of Access
you're using, how you populate comboboxes is different

Rather than an array of checkboxes checkbox(0), checkbox(1), etc., put 9
checkboxes on the form and name them checkbox0, checkbox1, and so on up to
checkbox8. Rename each of the associated labels label0, label1, and so on up
to label8

When you create the combobox, set its RowSourceType property to Value List.

Use the following VBA code:

Private Sub Command1_Click()
Dim looper As Integer
Dim rowsource As String

rowsource = vbNullString
For looper = 0 To 8
If Me.Controls("checkbox" & looper) = True Then
rowsource = rowsource & Me.Controls("label" & looper).Caption & ";"
End If
Next looper
' If you added anything to rowsource, it's going to have an superfluous
' semi-colon at the end that you need to remove
If Len(rowsource) > 0 Then
rowsource = Left$(rowsource, Len(rowsource - 1))
End If
Me.cbo.RowSource = rowsource

End Sub

If you're using Access 2002 or newer, you can simplify this to:

Private Sub Command1_Click()
Dim looper As Integer

Me.cbo.RowSource = vbNullString
For looper = 0 To 8
If Me.Controls("checkbox" & looper) = True Then
Me.cbo.AddItem Me.Controls("label" & looper).Caption
End If
Next looper

End Sub