Getting textbox data to change when date is manually changed.

[Mattbro]

Hello.
Background-In order to monitor productivity, I have a table listing 3 types of item by serial no, prefixed by H, N and G (e.g. H123/12, N345/12 and G123/12). Each item may be checked by 1 of 6 checkers (a,b,c,d,e and f) on a given day.
My table has columns for 'H', 'N', 'G', 'checker' and 'Datechecked'. I have queries for each checker (e.g. CountofH, CountofN, CountofG, DateChecked and Checker="a" etc). A form is based on each query with 'CountofH', 'CountofN' and 'CountofG'. These are placed on a 'Master form' which has a textbox bound to 'Datechecked'. I have a relationships from my table linking 'DateChecked' to all the queries. When I change the date in this box, all the textboxes update. All works. Lovely!
The problem-...when data is extracted to form the table, the types of serial number are in the same column rather than separated into 3 types. For the ease of users, I need to base the queries on that single column-'SerialNumber' rather than 'H', 'N' or 'G' columns. If I run a query for each type (Criteria [=H*] etc) and [Checker ="a"], it is fine until I ask for CountofType. No data appears.


My best results seem to be using three Crosstab queries, each based on the results of the type query with Checker heading the columns and DateChecked along the side. This is great
BUT
When I put these crosstab queries into forms and place them on the 'Master form', when I change the date in the textbox bound to 'Datechecked', the data doesn't change with it.
My first post-apologies if this is just confusing, or I have made errors, but any help or ideas would be gratefully appreciated.

[June7]

Crosstab queries are not editable. They are by nature aggregate data.

I don't understand "when data is extracted to form the table". What is happening?

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[Mattbro]

Many Thanks June7. The data is obtained from another database system and extracted as an Excel file. I have been 'sorting' the excel file prior to importing into Access. The role will fall to another and I am looking at importing the data by using an append function. Those performing the role need it simplified, hence the need to negate any pre-import sorting of the excel file, and my subsequent predicament! I have attached the file, included my type query (which doesn't work, but if it did, that would simplify the query process). As I mentioned, the previous incarnation of this file works fine, and I cannot see what I am doing wrong, and how to get the values to adjust with date change. Any pointers/alternative routes gratefully received!

Crosstab queries are not editable. They are by nature aggregate data.

I don't understand "when data is extracted to form the table". What is happening?

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[June7]

The table you provide does not quite fit description. There are no columns H, G, N and there should not be.

Correct syntax for the Type query you attempted:
SELECT Count(Data.SerialNo) AS CountOfSerialNo, Data.[Date Checked]
FROM Data
WHERE (((Data.Checker)="J") AND ((Data.SerialNo) Like "hg*"))
GROUP BY Data.[Date Checked];

If you want to do a count of the HH, HG, HN types then construct an expression in query that extracts the type code and use that field in a GROUP BY, like:
TypeCode: Left(SerialNo, 2)

And then I get lost on what you are really trying to accomplish. Your MasterForm doesn't make sense. All that is happening with the date edit is the date of one record is changed. Are you trying to edit the date for all records of particular type code, checker, and date? Accomplishing that en masse will require an UPDATE sql action.

[Mattbro]

My apologies, June7-In removing confidential data to make a model for the attachment, I have made errors-(H,G and N are synonymous for HH, HG and HN-sorry about that!). The version I attached is a model of the one I am trying to get to work, rather than the one that does work. The 'working' file has 3 columns-H, G and N. The modification I am trying to make work, must have only one column containing all H,G and N prefixed serial numbers.

Your SQL code for the type query is exactly what I wanted-I would very much like to know what I was doing wrong with the Type query in the attachment-it seems to me to be a good reason to start learning SQL!

Anyway, what I am trying to accomplish is when the user looks at the MasterForm and changes the date in the 'DateChecked' box at the top, all the boxes below, relating to the queries, will display the data relative for that day, (not modify the data in the table, hence no need for [update]). This works in the original file, with the serial numbers separated into three columns....I can enter the date, and see what each checker did on that day. I assumed that it stopped working in the modified file because of my query design...relationships are the same. I have attached a 'working model' of the working version (it's clanky, but does the job). Any input gratefully recieved!
Mattbro

[June7]

View the two queries in design view and the differences should become apparent.

Getting the 3 fields combined into one is a good idea. Create the SerialNum field in Data table. Build and run UPDATE: UPDATE Data SET Data.SerialNum = Nz([HG],Nz([HH],[HN]));

Can delete the 3 fields.

Test on a copy first but it worked for me.

If you want to use the date textbox just as a control for display of subforms and not to edit record, then can't bind it to field. Otherwise you are changing the value in the record.

[Mattbro]

Yes-I see the changes in query design.....well, that has taught me a thing or two about query design! I am nearly there, except if I unbind the textbox that I am using for the date control, then I cannot scroll through the records....any suggestions? Perhaps if I were to use a different control such as a button, then the user could move forward through the records....or backwards..... What I could really do with is getting buttons to advance the 'date checked' rather than advance each record. I reckon I could do that if I changed the design of the table-or maybe by feeding the table data into another query....if I place a textbox on the Masterform, can I use the expression builder to bind it to a query without getting the [#Name?] readout? If so, I could negate the need for subforms.
Thanks for your input June7!

View the two queries in design view and the differences should become apparent.

Getting the 3 fields combined into one is a good idea. Create the SerialNum field in Data table. Build and run UPDATE: UPDATE Data SET Data.SerialNum = Nz([HG],Nz([HH],[HN]));

Can delete the 3 fields.

Test on a copy first but it worked for me.

If you want to use the date textbox just as a control for display of subforms and not to edit record, then can't bind it to field. Otherwise you are changing the value in the record.

[June7]

Even if the textbox is unbound, you can still navigate through the records, you just don't see the data because no controls are bound. Add textboxes that will show the data of the main form RecordSource. You can lock them against editing.

Instead of linking the subforms to the textbox, link them to the date field of the RecordSource. I always name controls different from the field names. Use the unbound control (textbox or combobox) as input for criteria to either filter the RecordSource or move to record. Here is one technique for the filter approach http://datapigtechnologies.com/flash...tomfilter.html

[Mattbro]

Right you are, June7. I have just completed the queries in the working model and am looking at the video for comboboxes now. Thanks very much for your help on this issue. It has shown me a lot. I will study the video and look at alternative methods for record display. You are a star.
Mattbro

Even if the textbox is unbound, you can still navigate through the records, you just don't see the data because no controls are bound. Add textboxes that will show the data of the main form RecordSource. You can lock them against editing.

Instead of linking the subforms to the textbox, link them to the date field of the RecordSource. I always name controls different from the field names. Use the unbound control (textbox or combobox) as input for criteria to either filter the RecordSource or move to record. Here is one technique for the filter approach http://datapigtechnologies.com/flash...tomfilter.html