loop through textbox with different criteria (1 Viewer)

qupe

Registered User.
Local time
Today, 13:43
Joined
Feb 15, 2016
Messages
51
hi

i have 8 textboxes and i have 16 append queries.Two for each one of the textboxes.
Each one of these Queries has a criteria equal to the textbox.

is it possible to write a loop to all textboxes and change the criteria depend on the textbox name? and i will use just two append queries

textboxes name : subject1,subject2......
criteria : =subject1,=subject2,..........

i hope it is clear :D:D
 

Ranman256

Well-known member
Local time
Today, 16:43
Joined
Apr 9, 2015
Messages
4,339
Text boxes are for 1 entry.
Multiple items should be stored in a sub table,
1 item per record.
 

qupe

Registered User.
Local time
Today, 13:43
Joined
Feb 15, 2016
Messages
51
Unfortunately i got the data base like that multiple entries for one record :banghead:
 

qupe

Registered User.
Local time
Today, 13:43
Joined
Feb 15, 2016
Messages
51
i hope someone tells me the solution :D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:43
Joined
Oct 29, 2018
Messages
21,358
One approach is to store the textbox names (or the values in them) in an array and then loop through the array.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:43
Joined
May 21, 2018
Messages
8,463
This should be pretty easy in code. Can you provide the SQL for both types of queries for 2 different controls.
 

qupe

Registered User.
Local time
Today, 13:43
Joined
Feb 15, 2016
Messages
51
Code:
Private Sub Command200_Click()
Dim strSql, strSqlfees As String, i As Integer

For i = 1 To 8
'    strSql = "INSERT INTO [Copy Of tblsyllpus] ( Studentname, subject, ExamDate, enddate, Qualification, Syllabus, Code, [Component Title], [Session], Duration, gradyear )" & "SELECT [Form responses 1].ID, tblsyllpus.SubjectText, tblsyllpus.ExamDate, tblsyllpus.enddate, tblsyllpus.Qualification, tblsyllpus.Syllabus, tblsyllpus.Code, tblsyllpus.[Component Title], tblsyllpus.Session, tblsyllpus.Duration, [Form responses 1].[Year Group]" & "FROM [Form responses 1], tblsyllpus WHERE ((([Form responses 1].ID)=[Forms]![Form responses 1]![ID]) AND ((tblsyllpus.SubjectText)=[Forms]![Form responses 1]![Subject  " & i & "]))"
'         DoCmd.RunSQL strSql
    strSqlfees = "INSERT INTO [tblfees] ( StudentName, Subject, Fees, FormId )" & "SELECT [Form responses 1]![First Name]&' '& [Form responses 1]![Last Name] AS Expr1, subjects.Subjetc, subjects.Fees,[Form responses 1].ID" & "FROM subjects, [Form responses 1]" & "WHERE (((subjects.Subjetc)=[Forms]![Form responses 1]![Subject " & i & "]) AND (([Form responses 1].ID)=[Forms]![Form responses 1]![ID]))"
         DoCmd.RunSQL strSqlfees
Next i
End Sub

my problem is with strSqlfees although it does not show any wrong, it does not work when i run it.
i appreciate any help
 

Micron

AWF VIP
Local time
Today, 16:43
Joined
Oct 20, 2018
Messages
3,476
my problem is with strSqlfees although it does not show any wrong, it does not work when i run it.
If I recall correctly, this method doesn't balk (e.g. produce a parameter prompt) when fields are incorrect. Maybe the issue is spelling?
subjects.Subjetc
StudentName, Subject, Fees, FormId


Still, would suggest to OP to follow and try the linked method as I see other issues as well.
 
Last edited:

qupe

Registered User.
Local time
Today, 13:43
Joined
Feb 15, 2016
Messages
51
Here's a common debugging method:

http://www.baldyweb.com/ImmediateWindow.htm

Post the finished SQL here if you don't see the problem. It would also help to know exactly what "does not work" means.

thanks for your suggestion it was helpful.I found some mistakes ,however that did not fix the issue.
"does not work" means the SQL do not append new records to the table.
but i change some of the names like instead of "subject 1" i used "subject_1" which help to fix it.
thank you very much all ;)
 
Last edited:

Users who are viewing this thread

Top Bottom