Design question

[Rabbit]

Weird. Ok, it's because it's a date formatted text box. Put a CDate() around the form control reference and it should work fine.

[Seth Schrock]

Okay, here is my code:

SELECT tblFamily.FamilyID
, CDate(Forms!frmAttendance!txtAttendanceDate) AS DateAttended
FROM tblFamily
WHERE tblFamily.Attended = True;

And here is the error message I'm getting:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric express may contain too many complicated elements. Try simplifying the expression by assigning parts of the express to variables.

[Rabbit]

Try this, this worked for me:

CDate([Forms]![frmAttendance]![txtAttendanceDate])

[Seth Schrock]

I still got the same error message.

SELECT tblFamily.FamilyID
, CDate([Forms]![frmAttendance]![txtAttendanceDate]) AS DateAttended
FROM tblFamily
WHERE tblFamily.Attended = True;

I even tried leaving off the alias in case that was what you meant. Same thing. I tried creating a new query in case something had gotten stuck. Same thing.

[Rabbit]

I've attached my copy. The only change I made was the one in my post. I'm using Access 2007.

[Seth Schrock]

There must be some glitch in Access 2010. I just tried yours and I got the same error message.

[Rabbit]

Must be, it was running fine on mine.

[Seth Schrock]

I guess I'll have to wait to see what Z comes up with. I haven't been able to figure out how to make it work.

[zmbd]

Oh....
I had been trying to follow the thread and noted that there had been some work and the kids were very "me-me-me" so stopped.... sorry.

Give me a few moments to get back in the groove
What I have so far is two sub-forms, one showing the retreat information that you can enter or select
The second shows the families and the check boxes
Just working on some code to sync the two forms etc...
Once again it's not normalized so there's some extra work to get things to be happy.

[zmbd]

I haven't forgotten... been a busy weekend.

Just finishing putting togeither the logic behind the append to the attendance table... only another couple of minutes of codeing but I need to get some sleep... starting to type the same SQL twice in a row (zleepy Z) All of the queries work and I want to clean up the test entries before posting.

I'll have to get back to this Monday night after I get home. The boss is away on conference and we've auditors so I'll have very minimal time to even get to the email... :)

[zmbd]

here we go

Once again.... this database isn't normalized in that I stripped the tables out that had retreat details and fields that held family information.

When the database is opened the main form will also open.
Two subforms (neither directly linked)
Upper form, basic form access to the retreats. Realtime addition and deletion of the records; however, if any family is assigned to an attendance then the record can not be deleted.

Lower form, same thought as above with the checkboxes.

The rest should be straight forward from the form.

Behind the scenes... a few update and delete queries for the magic. The realtime update between upper and lower forms handled with on current event and so forth.

Feed back always welcome.

Started to use the new navigation form control; however, I haven't quite got the hang of it and I only had about an hour here to tie this up between sample runs and other projects.

[Seth Schrock]

That is incredible! It is taking me awhile to figure it all out. So far, my only question is: In the frm_retreat On_Current event, do I need the

Select Case zstring

portion, or is that just for parts that you stripped out of the database? I know I need what is inside the

Case Else

part, but what about the rest?

[zmbd]

how to bodge and sync

ahhh my dirty little secret...

The entire select case statement is needed to link the two subforms.
zstring = Nz(Form_frm_mai ndataentry.ztxt _currentretreat record.Value, "")
I'm using the text box as a semaphore and user feedback.
So I pull the current contents of the textbox and I'm looking for "update pending" or "move to last"
When the user enters a new record there are few things that can happen... if the user tabs thru, then you get to the new record, if the user does a <shift><enter > to save etc.... so when the new record is saved, the after insert event fires that tosses "update pending" in the mainform text box and kills any user sort; however, the user has moved the cursor and there's no way to stop it, so now the on current event fires, so I check for the update message, trapping the movement per say, now I flag "move to last" that I want to move to the last record in the form.... requery, which causes the on current to fire again and I get the move, which cause the on current to fire again, and now I can pull the information needed to sync the yes/no field in tbl_family with the entries in tbl_attendance. If the user has moved to the new record then the text box states blanks if the user cursored up then the cursor moves to the new(ly) inserted record and the information for that is shown.

There has to be a better way... and I've not found it.

The form's open event checks to make sure that the main form is open and if so then it moves to the first record then moves to the last.... that way the scrollbars show up; if one simply moves to the last record, the scroll bar doesn't show up until one hits the first record... annoying little glitch.

Clear as a Tar Pit?

[Seth Schrock]

So the Select Case isn't looking at the value, but the status (or whatever the correct term would be) of the record? If so, a tar pit might be too dark, but a mud hole might be appropriate. Way above my head anyway. Well, I will try to get everything switched over tomorrow and see if it works on mine (or more accurately, if I can get it to work).

A million thanks for all the effort you have put into this Z!

[zmbd]

The best way to watch the action:
In the after insert event of the frm_retreat insert a Stop right at the beginning - save
Close all the forms. Re-open the main.
The form opens with the last record in the frm_retreat selected and the textbox under the form showing the retreat information.
Enter a new record using the {tab} key throughout... once you move to the new record, the after insert event fires.
The stop command sends you to debug... now arrange the windows so that you can step the code.
The "update..." text is entered into the main form - user might see this might not.
Any sort order is cleared
<1>Now the on current fires
All selected families are cleared
The "Update..." is read from the textbox
Select case deals with this cycle by:
The flag "Move..." is entered into the textbox
The frm_retreat is re-queried
<2>Now the on current fires due to the re-query
All selected families are cleared (should move this)
The "Move..." is read from the textbox
Select case deals with this cycle by:
setting the textbox to an empty string
moving to the last record
<3>Now the on current fires due to the record move
Case else trips
We check to see if we're on a new record (no)
so we build the query string that pulls from the record we're on (which should be the last inserted record) and we update the family subform.
recursion <3> ends
recursion <2> ends
recursion <1> ends
On current ends
After insert ends
...
Shift-Enter - no record movement, the cursor stays with the inserted record and the "Update Pending" flag is shown. This is fine as the current record is correct and the user can simply select the families.
...
Cursor up to a prior record:
Basically the same as the tab thru to new... in that it runs the loops and then puts the user on the newly inserted record.
...