Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: Auto Populate Excel Userform fields from access query result
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: Auto Populate Excel Userform fields from access query result
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: Auto Populate Excel Userform fields from access query re
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: Auto Populate Excel Userform fields from access query re
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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP PLEASE: Populate certain userform fields from access database | Excel Programming | |||
HELP Error: Trying to populate userform fields from access databas | Excel Programming | |||
populate some userform fields from access database | Excel Programming | |||
Populate userform fields from access database | Excel Programming | |||
HELP JOEL: auto populate certain userform fields from access datab | Excel Programming |