Solved Loop through continuous form and assign value to textbox (1 Viewer)

Kayleigh

Member
Local time
Today, 22:51
Joined
Sep 24, 2020
Messages
706
Hi
I have a continuous form based on a crosstab query. The data at the moment is just displaying ID numbers, however it is simple to lookup more meaningful data from the ID. However I am having trouble looping through the fields and assigning the new data to the textbox. When I try all the boxes are filled with the same text. How can I improve this?
Code:
With Me.Recordset
  .MoveFirst
  Do While Not .EOF
    If Not IsNothing(Me.Monday) Then
      
      subj = DLookup("fldSubjectName", "qrySessionsAllExtended", "[fldSessionID] =" & Me.Monday)
      Debug.Print subj
      room = DLookup("fldRoomName", "qrySessionsAllExtended", "[fldSessionID] =" & Me.Monday)
      Debug.Print room
      stf = DLookup("cfStaffName", "qrylkpStaffWithTitle", "[fldStaffID] = " & DLookup("fldStaffID", "qrySessionsAllExtended", "[fldSessionID] =" & Me.Monday))
      Debug.Print stf
      Forms!frmStudentTimetable!txtMon.Value = Me.Monday
      Me.txtMon.Visible = True
      
      
    End If
    .MoveNext
  Loop
End With
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:51
Joined
Oct 29, 2018
Messages
21,358
Hi. The short answer is to use a temp table instead.
 

GinaWhipp

AWF VIP
Local time
Today, 18:51
Joined
Jun 21, 2011
Messages
5,901
What new data? If it's a simple look-up from the drop down why aren't you showing it in the combo box instead of the ID? Or you could just show the specific column from your combo box in the text control. What am I missing?
 

Kayleigh

Member
Local time
Today, 22:51
Joined
Sep 24, 2020
Messages
706
At the moment my closest solution is to use calculated fields which are hidden and then do a concatenate to show the results of these textboxes. But it is slow to load the form...
Can you elaborate on best way to use a temp form - is it to store the results of the records?
Maybe I should just create a new query for this?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:51
Joined
May 7, 2009
Messages
19,169
create another Query from your crosstab and Join the Other tables invovled.
 

Saphirah

Active member
Local time
Today, 23:51
Joined
Apr 5, 2020
Messages
163
Hi
I have a continuous form based on a crosstab query. The data at the moment is just displaying ID numbers, however it is simple to lookup more meaningful data from the ID. However I am having trouble looping through the fields and assigning the new data to the textbox. When I try all the boxes are filled with the same text. How can I improve this?
Code:
With Me.Recordset
  .MoveFirst
  Do While Not .EOF
    If Not IsNothing(Me.Monday) Then
     
      subj = DLookup("fldSubjectName", "qrySessionsAllExtended", "[fldSessionID] =" & Me.Monday)
      Debug.Print subj
      room = DLookup("fldRoomName", "qrySessionsAllExtended", "[fldSessionID] =" & Me.Monday)
      Debug.Print room
      stf = DLookup("cfStaffName", "qrylkpStaffWithTitle", "[fldStaffID] = " & DLookup("fldStaffID", "qrySessionsAllExtended", "[fldSessionID] =" & Me.Monday))
      Debug.Print stf
      Forms!frmStudentTimetable!txtMon.Value = Me.Monday
      Me.txtMon.Visible = True
     
     
    End If
    .MoveNext
  Loop
End With
Hey, so you can not assign individual fields on a continous form. A continous form is very optimized, because it will render row by row once.
This means that if you assign a value to a field, your code is executed BEFORE the list is rendered, which causes all the fields to become the last value you assigned to the field.

Long story short: If you want to add data, then put them in your query. Join your tables using your SessionID.
Basically do the same you did with your DLookup using Query Joins!
 

Kayleigh

Member
Local time
Today, 22:51
Joined
Sep 24, 2020
Messages
706
Thanks for that.
In the end I created two similar crosstab queries - one with details and other with ID values (to link to other forms) and joined together in a final query which will be what my form is based on.
Seems to be working well!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:51
Joined
Feb 19, 2002
Messages
42,976
I'm guessing that you have a lookup field and you think the crosstab should be showing the text value. But it doesn't. That is just one of the reasons experts don't use lookups on table fields.
1. get rid of the lookup
2. Join the table you want to crosstab to the table with the text value to pick up the text value.
3. crosstab the query rather than the table.
 

Users who are viewing this thread

Top Bottom