Sam,
Once you get the data into an array, it's easy to loop through the array.
An array is capable of holding far more dimensions than you'll ever need, but
you generally don't need more than 2 dimensions (and on occasion 3). So, one
way to think of an array is by using a row, column format. A one-dimensional
array is simply multiple rows in a single column (or your vaData = .GetRows
in your code). A two-dimensional array would be multiple rows and multiple
columns. You can loop through arrays using the index structure. The
functions LBound and UBound come in very handy for this. The functions take
two arguments, the arrayname and the dimension. (See LBound and UBound in
VBE Help).
Assigning one of the array values to a text box is simply a matter of
assigning the Value property of the text box to an indexed array value. For
example, let's assuming you have a user form (UserForm1) with a single text
box (TextBox1) and an array of values (intArr). Putting one of the values
into the text box could look as follows:
UserForm1.TextBox1.Value = intArr(0)
Now, I'm anticipating that you may want to feed the UserForm data back to
Access (via some manipulation statement, such as INSERT), so I've put
together a very crude example that will illustrate an alternative method to
what you have described.
1. Open a blank workbook and insert the following data (i.e. a "dummy"
database) into Sheet1:
A1: StudentNum; B1: Grade; C1: Course
A2: Student112; B2: B; C2: Math101
A3: Student113; B3: A; B3: English101
A4: Student114; B4: C; C4: Science201
2. Open the VBE
3. Insert a UserForm (VBE: Insert|UserForm) - UserForm1
4. Place a ListBox control on the UserForm - ListBox1
5. Place three TextBox controls on the UserForm - TextBox1, TextBox2, TextBox3
6. Place one CommandButton control on the UserForm - CommandButton1
7. Right-click the UserForm and select View Code
6. Paste the code below into the code window
7. Run the UserForm_Initialize procedure (i.e. place the cursor in the
procedure and press F5, the "play" button, or Run|Run; you can audit the code
step-by-step via hitting F8 repeatedly)
8. Select a ListBox entry
9. Change one (or more) of the TextBox entries
10. Click the CommandButton (and watch the selected item in the ListBox
change)
11. See an illustrative way to loop through the ListBox items below (i.e.
PrintValuesInListBox procedure). The values are printed to the Immediate
Window (View | Immediate Window) each time you hit the X in the upper-right
corner of the UserForm
12. Click the X in the upper-right corner of the UserForm to close the form
Let me know this is helpful and/or if you need more assistance. You'll have
to step through/audit the macros to see what is going on, but I believe that
you should be able to follow along fairly easily. Again, this is a very
crude example that simply illustrates an idea.
Best,
Matt
'------------------------------------------
'Code window syntax
Private Sub CommandButton1_Click()
Dim lngCnt As Long
With Me
With .ListBox1
For lngCnt = 0 To .ListCount - 1
If .Selected(lngCnt) Then
.List(lngCnt, 0) = Me.TextBox1.Value
.List(lngCnt, 1) = Me.TextBox2.Value
.List(lngCnt, 2) = Me.TextBox3.Value
End If
Next lngCnt
End With
End With
End Sub
Private Sub ListBox1_Change()
Dim lngCnt As Long
With Me
With .ListBox1
For lngCnt = 0 To .ListCount - 1
If .Selected(lngCnt) Then
Me.TextBox1.Value = .List(lngCnt, 0)
Me.TextBox2.Value = .List(lngCnt, 1)
Me.TextBox3.Value = .List(lngCnt, 2)
End If
Next lngCnt
End With
End With
End Sub
Private Sub UserForm_Initialize()
Dim varArr As Variant
varArr = Worksheets(1).Range("A2:C4")
With Me.ListBox1
.ColumnCount = UBound(varArr)
.List = varArr
.MultiSelect = fmMultiSelectSingle
End With
End Sub
Sub PrintValuesInListBox()
Dim lngRow As Long
Dim lngCol As Long
With UserForm1
With .ListBox1
For lngCol = 0 To .ColumnCount - 1
For lngRow = 0 To .ListCount - 1
Debug.Print "(" & lngRow & "," & lngCol & "):" &
..List(lngRow, lngCol)
Next lngRow
Next lngCol
End With
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
PrintValuesInListBox
End Sub
'------------------------------------------
sam said:
Hey Matthew,
Thanks for your Help
I got to make it to work such that It populates a listbox with the Students
Grades But not able to get what I really want ( populating it in seperate
text boxes).
The thing you said about storing the query outputs into an array, Could we
do that and then make it populate into seperate text boxes? OR
How about we store the outputs in seperate variables and then populate the
texboxes? I am not an expert in VBA so am not able to get my ideas into code.
But I think it should be possible somehow...
About storing it in a list, I did get it to work that way, But am struggling
with displaying multiple column data into the same list box
for eg: Student112 - B - Math101
Here Student112, B, Math101 are Sudent Id, Grade, and Course which are
stored in seperate columns in the database.
Also One more reason of not having it in a list box is, (once we pull the
data from access into the form) we want to eventually edit the data if we
want to, and I dont know if that would be possible with populating the info
in a list box.
Here is the piece of code that populates the listbox with students grades
Dim strSQL As String, conn As Object, rst As Object, k As Long, vaData As
Variant
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C\Documents\Students\Students_Data.accdb;"
strSQL = "SELECT Students_Grades" & _
" FROM Students_Info WHERE Student_ID = " & _
Me.StudentId.Value
Set rst = conn.Execute(strSQL)
With rst
Set .ActiveConnection = Nothing
k = .Fields.Count
vaData = .GetRows
End With
conn.Close
With UserForm1
With .listBox1
.Clear
.BoundColumn = k
.List = Application.Transpose(vaData)
.ListIndex = -1
End With
End With
Thanks in advance
Matthew Herbert said:
Sam,
Have you considered using a ListBox instead of multiple text boxes? I have
never done an Access query from Excel, so I don't know what the result is
returned as (i.e. an object, a delimited string, etc.); however, I'm sure
that parsing the result will be easy enough.
The VBE Help for "List Property" shows you how this property can be utilized
for a ListBox control. Specifically, you can load the list one item at a
time (via the row/column index), or you can use an array to load the list all
in one shot. If you use the later (i.e. the array approach), then I suggest
setting the ColumnCount property prior to setting the List property. (I've
run into situations where a multi-dimensional array would load correctly into
the List property only if I set the ColumnCount prior to setting the List
property). Take specific note that the row/column numbering begins at zero.
Let me know if this helps (or if you need me to create a dummy Excel to
Access query to determine how to get the query data parsed into the ListBox,
which would force me to learn something that has been on my to-do list).
Best,
Matthew Herbert
sam said:
Hi All,
How do I populate Access query results in excel userform text fields?
For eg;
I have a Query: "SELECT Student_ID, Course, Grade, Qtr_ID" & _
" FROM Student_Info WHERE Student_ID = " & _
Me.StudentId.Value
Here Me.StudentId.Value is a text box in excel userform
And here is what the query results look like:
Student_ID Course Grade Quarter_ID
JP1124 Math A SP01
SP1164 Phy B SP01
Here "Student_ID, Course, Grade, Quarter_ID" are the column headers in Access
The userform looks like this:
Student ID: [ ]
Course Grade Quarter_ID
[ ] [ ] [ ]
[ ] [ ] [ ]
[ ] [ ] [ ]
So the query results will go into different textboxes [ ]
Hope I made it clear,
Thanks in advance
Thanks in advance
