Best way to...

[Jaysoul]

Hi all,

I am going to be designing a user form.

I want to run a query and display the resulting recordset on the form. It will be multiple columns of data so was planning to use a listbox.

What I then want to be able to do is present the user with a combo box of options corresponding to each row of the record set, and then allow the combo box to be selected and its value written back to the approprtiate row in the database.

Could anyone please advice me on ideas of how best to achieve this please?

I haven't started on this yet, but I am wondering how I will associate each combo box with each row of the record set.

Thanks,
Jay

 

[pbaldy]

Could the selection be different for each row, or a single selection affects all rows? It sounds like each row could have its own selection, in which case I might consider a continuous form (or subform) instead of the listbox.

 

[Jaysoul]

Could the selection be different for each row, or a single selection affects all rows? It sounds like each row could have its own selection, in which case I might consider a continuous form (or subform) instead of the listbox.

Yes, each row could have its own selection from the combo box.

Thanks, I will look into continuous and subforms - I don't know them.

 

[pbaldy]

No problem; post back if you get stuck.

 

[Jaysoul]

Thanks for the previous advice.

I now have a continuous sub form within a main form.

My question now is: how do I populate the text boxes on my continuous sub form with data held in a recordset from a query.

I can't use the Control Source or Record Source in the properties menu because I need to pass parameters to my query within VBA.

I have tried this:

For c1 = 1 To noofrows
Me.frm_grades.Form.txt_id = recGrades(0)
Me.frm_grades.Form.txt_name = recGrades(1)
recGrades.MoveNext
Next

But this appears to keep populating the same text boxes rather than creating new ones depedning on how many rows there are in the recordset.

Can any point me in the right direction please?

Regards
Jay

 

[Jaysoul]

BUMP! Can anyone help with this please?

 

[georgedwilkinson]

I don't really understand the issue. Is your combo-box bound to a field in your sub-form's query? If not, it will behave the way you say. Continuous forms are pretty unforgiving about what fields you put on them.

Also, I'm a bit confused as to why you need VBA at all. Once the tables are properly normalized and the forms/fields are properly bound, this stuff generally just works without any programming.

 

[pbaldy]

Can you clarify what comes before this? I would expect you to be able to set the source one way or the other.

 

[Jaysoul]

Can you clarify what comes before this? I would expect you to be able to set the source one way or the other.

Thanks for getting back again Paul.

So do I just need to set the record source of the form to equal the sql statement then instead of opening it into a recordset?

Here is the full code for what I am trying to do with the subform so far:

Private Sub lbx_deliveries_AfterUpdate()

Dim targetdelivery As String
Dim db As Database
Dim recGrades As Recordset
Dim c1 As Integer
Dim noofrows As Long
Dim sqlstatement As String

targetdelivery = lbx_deliveries

sqlstatement = "SELECT student_details_t.stud_id, student_details_t.stud_name, student_details_t.stud_sex, student_details_t.stud_dob, student_grades_t.cw_mark, student_grades_t.exam_mark, student_grades_t.total_mark, student_grades_t.calculated_grade, student_grades_t.actual_grade" _
& " FROM student_details_t INNER JOIN (deliveries_t INNER JOIN student_grades_t ON deliveries_t.delivery_id=student_grades_t.delivery_id) ON student_details_t.stud_id=student_grades_t.stud_id" _
& " WHERE (((student_grades_t.delivery_id)=" & targetdelivery & "));"

Set db = CurrentDb()
Set recGrades = db.OpenRecordset(sqlstatement, , dbConsistent)

recGrades.MoveLast
noofrows = recGrades.RecordCount
recGrades.MoveFirst

For c1 = 1 To noofrows
Me.frm_grades.Form.txt_id = recGrades(0)
Me.frm_grades.Form.txt_name = recGrades(1)
recGrades.MoveNext
Next

End Sub

 

[pbaldy]

That's what I would do, or even have that query as a parameter query as the source of the subform, and just requery it here.

 

[Jaysoul]

Again, thanks for the help guys.

Two thoughts:

1. I never considered that this could be done with no VBA at all. I guess I need to research how to use paramter queries with subforms?

2. If I do continue down the VBA route and I set the record source of the subform to my sql query within VBA, can I then individually set the control source of my text boxes to the fields in the query using VBA? And if so how do I do this?

Thanks

 

[pbaldy]

1) I'm talking about having the record source of the subform being a query:

SELECT...WHERE delivery_id = Forms!FormName.lbx_deliveries

2) Since the SQL doesn't appear to change, why wouldn't you just leave them bound to the fields all the time?

 

[Jaysoul]

1) I'm talking about having the record source of the subform being a query:

SELECT...WHERE delivery_id = Forms!FormName.lbx_deliveries

2) Since the SQL doesn't appear to change, why wouldn't you just leave them bound to the fields all the time?

Nail on the head there I think Paul...

::the penny drops!::

Thank you!

 

[pbaldy]

No problemo, post back if you get stuck.