Need help with form based on query

[Noreene Patrick]

Now I need help on the query for the form

I have not worked in Access for a while and cant seem to remember how to do simple things!! I have searched before I posted this and nothing seems to be exactly what I am looking for so here goes.

I have a table (tblmembersinfo) holding name, address, birthdate, class..and I want to pull out names by the class they are in.

I based my form on a query that has name and class from table but in criteria I entered [enter class] so it sorts and only pulls up names from that class when I open my form.

But, I also need the form to allow me to add date and a checkbox for attendance and since it is based on a query, it wont let me check the box or either when I check one record it checks all the records that are pulled up.

I want the form to append this data to a history table (that is empty right now) so I can get percentages later. I know how to write an append query but nothing seems to be working correctly..I even tried a combo box that fills in the info but still I cant get the date and attendance checkbox to work.

Do I need to open form with something other than a query?

I have done this before but for the life of me I cant remember how I did it.

Thanks in advance.

 

[ajetrumpet]

But, I also need the form to allow me to add date and a checkbox for attendance and since it is based on a query, it wont let me check the box

That will happen if it's non-updatable.

or either when I check one record it checks all the records that are pulled up.

That will happen if your checkbox is unbound.

I want the form to append this data to a history table (that is empty right now) so I can get percentages later. I know how to write an append query but nothing seems to be working correctly

Need more information on the process you used that isn't working correctly.

 

[Noreene Patrick]

Thanks, ajetrumpet..

You are correct!! I didnt have my form checked to allow data entry and my checkbox was unbound..It seems to just need some tweaking now.

Thank you again.

 

[Noreene Patrick]

I have a query for the combo box that is based on tblmembersinfo that pulls name and class. But there are several names for one class. When I select a class from the combo box, it has the class in there several times ( the number of times listed) in the table. I need the query to combine all classes and return the names. I tried a crosstab query but just cant seem to get it right.

Maybe I should post this in query..but it does have to do with my form..

Thank you for your patience with me.

 

[ajetrumpet]

When I select a class from the combo box, it has the class in there several times ( the number of times listed) in the table. I need the query to combine all classes and return the names.

here's an example (assume the value called "Value1" is listed in the table 3 times):



The following rowsource will give you all three instances of "Value1":

SELECT table.field FROM table

This rowsource will give you only one listing of "Value1":

SELECT DISTINCT table.field FROM table

is this what you need to know?

 

[Noreene Patrick]

Yes, that does group the classes together...but, how do I get it to autofill the names that belong to that class?

I would like it to (on a continuous form) to list each name that is associated with that class so that all I have to do is check the checkbox for those that are present. I have the date box as =date() so that part works believe it or not!!

Thanks for your quick responses.

 

[ajetrumpet]

I think what you're trying to say here is that you've got subcategories of a main categories that you want listed. You are using the term "class" in mixed content here. "Class" is a sophisticated programming term, and it has nothing to do with what we're talking about here.

Can you give me any more? A picture or something?

 

[Noreene Patrick]

Sorry...you are on the top rung of the ladder and I am on the bottom and digging..

My term for class is a field in my table(tblmembersinfo). The table fields are name, class, date and attendance.

I have several names in the table that are in one class..for instance, there are 10 children in class Children III

I want my data entry form to pull out names and classes from tblmembersinfo and based on the class I select from my dropdown box, auto fill the names of the children listed for that class. My form controlsource is a table (tblattendance) that holds each weeks data for percentage purposes.

I cant figure out how to make my query (combo box based on) group the classes (your suggestion was correct on that) and then display every name for that class so all I have to do is click the checkbox for attendance and the date is already set for =date().

Does that clarify my predicament?
PS My query is qryclass that pulls name, class, date and attendance fields from tblmembersinfo. That query is what I used for combobox.

Thank you.

 

[ajetrumpet]

I want my data entry form to pull out names and classes from tblmembersinfo and based on the class I select from my dropdown box, auto fill the names of the children listed for that class.

I understand everything now Noreene. The only question left to ask is:

Autofill the Childrens' names WHERE?

 

[Noreene Patrick]

I want it to autofill the names to my form that is based on the tblattendance. My form's controlsource is tblattendance. The fields for that table are name, class, date and attendance. The same as tblmembersinfo except tblmembersinfo also includes address, etc.

 

[ajetrumpet]

There is not a problem with this Noreene. The form is a data entry form, correct? Do you want 1 new record per name, or something else?

 

[Noreene Patrick]

What I would really like is that when I open my data entry form, it asks what class..then I type in class (ex. Children III or select it from a dropdown) and it queries all those names from tblmembersinfo into the form, (at this point I'll take any thing, single form,continuous, (but, I would like for it to look like datasheet (as the query looks when it queries). But, I have to be able to check the checkbox on each record and have the current date display without typing it in...I want it to go to the tblattendance so I can have a table in which to show history for percentages.

I have done this before...I cannot seem to figure out how I did it.

Any help will certainly be appreciated.

Thank you

 

[Noreene Patrick]

ajetrumpet

I am sending a zipped db to see that will help. I need to be able to open form, click on dropdown, choose a class and have it autofill the detail section with name of everyone in that class.


Thanks

 

[ajetrumpet]

Noreene,

Your form is set up in kind of a strange way, so I did what I could. You probably won't be able to decipher the code that I put in, but go through the following steps and see if you like what it does:

1) Pick from the dropdown of available classes
2) Select the student, indicate attendance or not by the checkbox, and push "Record Attendance"
3) Select any other students if any are left in the list box. If there are none left for the class that you chose, the form will close and you will have to start over.

What do you think of this method??

 

[Noreene Patrick]

You are just great, ajetrumpet!!

YOU ARE WONDERFUL!!!! I just want to thank you so very much..it looks great!!!

I gave this thread a #5 rating because it was just what I needed!!

Thanks again, Adam

Noreene

 

[ajetrumpet]

Not a problem Noreene. Good luck with everything.

 

[Noreene Patrick]

Here it is again, ajetrumpet!!

 

[ajetrumpet]

Noreene,

The first thing you should know about your form is that it is probably going to have to be restructured, especially if you do not know how to code the database. Your current setup cannot flow efficiently without cumbersome blocks of code behind the form.

If you still want to keep it the way it is, that's fine, but things get even more complicated. I looked at the coding I wrote, and it was not all-inclusive to satisfy every condition you will run into when you are recording attendance. Make the following changes and everything should be fine (as for now):

Change the current listbox rowsource code from this:

Me.List25.RowSource = 

"SELECT tblMembersInfo.LastName & ', ' & tblMembersInfo.FirstName FROM tblMembersInfo " & _
  "WHERE tblmembersinfo.lastname & ', ' &  tblMembersInfo.FirstName <> " & _
    "('" & Left(Me.List25, InStr(Me.List25, ",") - 1) & "' & ', ' & " & _
      "'" & Right(Me.List25, Len(Me.List25) - InStr(Me.List25, ",") - 1) & "') AND " & _
        "tblmembersinfo.class = '" & Me.Combo21 & "'"

To this:

Me.List25.RowSource = 

"SELECT tblMembersInfo.LastName & ', ' & tblMembersInfo.FirstName FROM tblMembersInfo " & _
  "WHERE tblMembersInfo.LastName & ', ' & tblMembersInfo.FirstName NOT IN " & _
    "(SELECT tblattendance.name1 FROM tblattendance WHERE " & _
      "tblattendance.date1 = forms!frmAttendance!mydate) AND " & _
        "tblMembersInfo.class = forms!frmAttendance!combo21"

That should take care of the appending process and the requery errors. Let me know what you think...

<edit>
Just to make life easier, I uploaded the file with the new RS...

 

[Noreene Patrick]

PUUUUUUURRRRFECTTTT!!!!! You're just a dream, ajetrumpet!!!

Many thanks to you and when I grow up I want to be just like you!!!

Noreene