trouble with query and null recordset

[Mandamus]

Hello All



I'm new to access VBA and I have been trying to make this make this sub works but I hit the wall every time with two different problems.

1 - my query seems fine and with debug.print it gives me value but when I run the sub it shows that the recordset is empty which is not.
2 - if I remove the query and put the name of it instead and tried to use the filter on rst.filter I get the null value.

my table must have null values because we don't have inputs all the time and a teacher can create a class without having to assign specific subject or topic or even have an assistant. I have tried a continues form but it is not what i want though.

here is my code:

Private Sub Daily_Schedule1()






Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstFiltered As DAO.Recordset
Dim inQuery As String
Dim strStart As String


Dim i As Integer
Dim j As Integer


For i = 1 To 75
inQuery = ("SELECT * FROM [qryDailyClass] WHERE [WeekNumber] = '" & Me.txtWeekNum & "' AND [CourseType] = '" & Me.TxtLevel & "' and [ClassNumber] = '" & i & "'")
Debug.Print inQuery


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(inQuery)

strStart = "txtClassNum" & CStr(i)
Controls(strStart).Tag = i
Controls(strStart) = rst!Teacher & " - " & rst!Assistant1 & " - " & vbNewLine & rst!Subject & " - " & vbNewLine & rst!Topic & " - "

rst.MoveNext

Next i


rst.Close
Set rst = Nothing


End Sub


another problem is that I need the tag so I can open a continues form of the same record that is in the text box and I keep failing to make this sub too.

I will appreciate assistant on changing my code whenever anyone can see it.

thanks

Claude

[ranman256]

you dont need any code to do any of this.
This can be done in a continuous form. Set the query to filter whatever week you pick in a text box in the header.

the query:
select * from table where [weeknumber]= forms!myForm!txtWeek


control.tag does nothing.

[Mandamus]

Hello ranman256

none of the filters in the query can be obtained without running the form first.
the class number is a calculated field based on the date of entry and form design while week number is a value that appears only on the form.
I can not set the query to filter anything unless I run the form. besides, I need the fields to appear in two rows so I can not concatenate with Dlookup unless I use multiple dlookup which slows the result.

the only way for me is to go through a recordset and filter the query while doing so. I still can't get my query right with all where clauses and null value is always there

[John_G]

In [qryDailyClass], what are the data types of [WeekNumber], [CourseType] and [ClassNumber]? If they are numeric, than they query is incorrect, in that you have quotes around the values, making them into strings. The query should be:

inQuery = ("SELECT * FROM [qryDailyClass] WHERE [WeekNumber] = " & Me.txtWeekNum & " AND [CourseType] = " & Me.TxtLevel & " and [ClassNumber] = " & i )

Will those three fields always have values? In order for your query to select anything, it must have an exact match on all three conditions. A null in any one of them will cause a record not to be selected.

strStart = "txtClassNum" & CStr(i)
Controls(strStart).Tag = i

These two lines have me suspicious about your data structure - you have 75 controls on your form, called txtClassNum1, txtClassNum2, ....
Do these correspond to 75 fields in a table somewhere?

[Mandamus]

hello John

the criteria are always numeric as 2 of them are calculated from another form and one is a level that refers to 1,2 or 3.
I made an option box to represent the value of the level of the main form so when they check the desired level it is supposed to give data of that schedule like this :

Me.TxtLevel = 1


OpELP1.Value = True
OpELP2.Value = False
OpELP3.Value = False

I have tried the query after you update it and it still gives me an error.

the 75 text boxes represent daily classes but all are taken form 4 fields ( teachers, assistants, subject, and Topic), each text box should have one record from all those fields that match the week number and class number and level. I have tried the continuous form but the class is attached to a period of time. if I use a continuous form then I can not match the class to its period.


a continuous form will not make me able to match the text box to the period

by the way, I was able to do what I want with lookup but I couldn't write the correct criteria when I put three conditions altogether. I might be writing it correctly because it keeps telling me data mismatch type. I don't know where is the mismatch if all are numbers?

thank you for you reply

[ArviLaanemets]

The period is week? When yes, then it is easy to use subform (continious or single - as you prefer).

At first, what is Weeknumber you are quering in qryDailyClass? Looking at your example just above, it is an integer like 37. When yes, then consider replacing it everywhere with weeknumber in format yyyy.ww - p.e. 2017.37 - then you have less parameters to control. When you want, you can have Year, Month, Week start and Week end on your form as currently, but these will be unbound calculated fields.

Now create an unbound form (p.e. fMain), or use some single form with some table/query, which has field Class/Weeknumber. Insert the continious form into this unbound/single form as subform.

In case, you use unbound form as main form, add 2 combos at top to select Class and Weeknumber. In case you use single form bound to table/query, it is a bit trickier - you have to add a combo for selecting filtering condition for Class or Weeknumber and another one for simply selecting another parameter, and filtering the single form according selection made for one combo (use AfterUpdate events of combo to set filter for form).

Now, in added subform properties, you set links. When using unbound main form, LinkMasterFields = 'cbbSelectClass,cbbSelectWeek' (I'm not inster forms source, and 'cbbSelectClass'/'cbbSelectWeek' for field not present in main forms source, or you can use filtering and selecting combos for linking entirely - the lastoption is really same as the one with unbound main form, plus you can get p.e. additional class info displayed in main form.

When you want to keep Weeknumber as integer, all above will be same, except you must have 3 linking conditions in subform (Class, Year, Week), and an additional combo in main form too.

[Mandamus]

hello ArviLaanemets

I'm not sure I understood you well. if I use subform and link it to a query then how can I show each class alone?
we are a kindergarten, we have 15 periods a day ( including classes, lunch and all types and sorts of daily activities). each period of time during the day needs a teacher, an assistant and a subject and a topic ( yes even lunch and sleep they need that ). as a result, we can say we have 15 classes ( hypothetically classes) a day, in one week we have 75 classes. I could easily use excel and build a sheet of 75 classes to show the same as my form previously, but I need to build a database to store information and save time because later I need to add lesson plans and courses to it. I have tried several methods ( based on my little experience with access ) and all failed when I built a subform. besides, what's the point of a subform if my main form is unbound. I need my teachers ( whom knowledge with a computer and English is limited) to work just as they are seeing their previous excel datasheet

[June7]

Please post code within CODE tags to retain indentation and readability.

rst = Nothing because the Set rst line has not yet executed. Put breakpoint on the line following Set rst. Then when hovering over the rst variable the popup will not appear because it cannot show recordset content.

I still don't understand how dynamically setting Tag property is useful.

A BOUND subform on an UNBOUND main form can be useful. I have done that. However, you are populating an UNBOUND form because your data structure is not like the form?

If you want to cycle a recordset it must be Set outside the loop. Example:

Dim rst AS DAO.Recordset, x As Integer
Set rst = CurrentDb.OpenRecordset("SELECT * FROM ...")
For x = 1 To 75
      Me.Controls("txtClassNum" & x) = rst!Teacher & ...
      rst.MoveNext
Next x
rst.Close

The code presumes there will always be 75 records in the correct order to coincide with the control name sequence. If you are filtering the recordset to a single record then it can be set within the loop and must also Close it instead of the MoveNext. However, one call to open recordset is probably better than 75.

Provide your attempted DLookup() if you prefer that method. Domain aggregates can perform slowly with large dataset (again, 75 calls to pull data). Your SQL statement in post 1 has apostrophe delimiters for the parameters but the image does not - which is correct?

We could better advise if you described your data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

[ArviLaanemets]

if I use subform and link it to a query then how can I show each class alone?

On main form, class and week in unbound combos are at start automatically set to default values. In subform only data for this class at this week is displayed. Whenever you select a new class or week in combos, the info is refreshed accordingly automatically (in case the links between forms are set properly).

When you have some query as source for subform, which has info about all classes for all weeks, then linking the subform to main form effectifely works as setting additional WHERE condition to query - like ' ... WHERE [Class] = Me.Parent.cbbSelectClass AND [Weeknumber] = Me.Parent.SelectWeek' (of course there is really no additinal WHERE clause, but it works this way). And when you have a table or updatable query as subforms source, and have controls (hidden I advice) linked to [Class] And [Weeknumber], then when new record is added, these controls are populated automatically (but it looks like you don't need this - and probably have to disable inserting records in subform)

[Mandamus]

Thank you all for replying. Monday is the busiest day I have. I can not post details today but I will do tomorrow for sure. thank you all for your help