Box with Question Mark Inside

[ErinL]

Hello everyone!

I have the following query:

SELECT [forms]![frmObservation]![ObservationNumber] AS ObservationNumber, tblPerformanceActivities.ObservationType, tblPerformanceActivities.PerformanceActivityNumber, tblPerformanceActivities.PerformanceActivityDescription, tblPerformanceActivities.PerformanceActivityRating
FROM tblPerformanceActivities
WHERE (((tblPerformanceActivities.ObservationType) Like [forms]![frmObservation]![ObservationType]));

When I open frmObservation and start an observation, it assigns a number to the record in the ObservationNumber field (an AutoNumber field). However, when I run the query above, the ObservationNumber field is not carried over from the form. All I see is a square box with a question mark inside it in the ObservationNumber field.

I have never seen this before and can't see why it is happening. :banghead:

Can someone tell me why this won't pull the number from the form?

Thank you in advance.

 

[boblarson]

when I run the query above, the ObservationNumber field is not carried over from the form.

1. The form reference is not going to have the autonumber UNTIL the record is saved. Just because it shows in the form doesn't mean it is actually there.

2. You will need to issue a save command first

Me.Dirty = False

in order to have that value be available.

 

[ErinL]

I tried your suggestion but there was no change.

Even if I create a new observation, close the form and then go back in, it still doesn't work. The observation numbers (records) all exist in the table but it still won't recognize the observation number on the form in the query.

 

[boblarson]

I tried your suggestion but there was no change.

Even if I create a new observation, close the form and then go back in, it still doesn't work. The observation numbers (records) all exist in the table but it still won't recognize the observation number on the form in the query.

can you post a copy of the database (with fake data) so we can take a look?

If you need to clear out real data in a copy, you can use my free database reset tool
http://downloads.btabdevelopment.com/Tools/DatabaseReset.zip
which can reset a bunch of tables (saving a lot of work). And if you can upload the db here, remember to run Compact and Repair first and then zip the file (you can right click on the file and select SEND TO > COMPRESSED FOLDER). And then be sure to post what form(s) etc. to be looking at.

 

[ErinL]

Thank you so much for offering to look at this. The database is just in the beginning stages so there really wasn't much to clear out or make up and there aren't too many objects at all yet.

The form is frmObservation and the query is qryPerformanceActivitiesRating.

The plan is to have the form open blank (of course ) and then once the fields in the header are filled out, the subform (which is based on the query in question) populates with the appropriate list of activites based on the choice in the "ObservationType" drop down. The user will then enter a rating for each activity.

Thanks again!

 

[boblarson]

Okay, first off. You can't do what you have tried to do with the subform. What you need to do is to run an append query to the tblObservationDetail table to add the activities.

1. Observation Type should not be stored in tblObservationDetail. Only in tblObservation.

2. I did some revision to your table structure to more properly normalize it. Also, just an FYI - don't use Text for primary keys if you can help it. Use numbers. And also make sure to use the same datatypes - you had one field as Integer or Long Integer and the corresponding one in another table as Double. In this case they should both be Long Integer.

3. Look at your relationships to also see what I did.

4. And to add activities, make it so that when ready, click the Add Activities button.

See if this helps. The name text box can just refer to the SV Number combo's 2nd column (as is what I did).

Let me know if you have questions.

 

[ErinL]

Thank you for all the work.

This definitely sends me in the right direction. I understand what you did with the tables and the append query and when I rate each activity it does transfer over.

The only problem I saw is that the ObservationNumber and the ObservationType fields change to match the observation number and type in the header but the ActivityNumber and Description do not. The activities for the Letdown observation type are always there. When you change types, the description of the activities does not change with it.

I will play around with it today and see if I can figure it out.

Thank you very much for the time you spent! It definitely cleaned things up.

 

[boblarson]

If you need to have all activities there for someone so that when you change the observation type on the main form, then you will need to modify the append query to not use the control as the limiter and just append all records from the activities table to the details table for that observationID.