Temporary fields in a query

[garfieldsevilla]

I have large table from which I need to select some records based on options given by the user in a form. Filtering the table is no problem but the filtered records (around 300) need additional temporary fields. These fields will initially be set to the same value but the user needs the option of adjusting the fields for each record.

I started to program this by creating a temporary table, getting the user input and then copying across the records I need before finally updating the two additional fields with defaults:

DoCmd.RunSQL "INSERT INTO Rtmp SELECT DNI FROM Slst WHERE Est <10"
DoCmd.RunSQL "UPDATE Rtmp SET [score]= '" & Me.Score & "', [Res]=" & Me.Res

The final table is then presented in a form for the user to review and edit (only the two additional fields can be edited) before it is exported to a text file and the table is deleted. The text file is used by another program for further processing.

It works but I was wondering if there is a more elegant solution..

[hedges98]

I'm probably wrong but, couldn't you do this using queries/subqueries?

[garfieldsevilla]

hi Hedges98, could you pls elaborate?

My form takes input which is used to select the records from the main list. I am not sure how I can pass that input to the query- not familiar with that.

Also, where would I hold the two additional and temporary fields for each record selected?

I was thinking I could simply add two extra fields to the entire database and then just filter but filter list will be 200-300 and the main table is 2000+.

Sorry if I am missing an obvious point- quite new to Access programming.

[hedges98]

Could you post your table structures? I can help further if I can see them (well, I'll try, I'm no expert myself!)

You can create a query that will take the selection from the combo box on your form and filter the results (along with creating the new fields you require).

From this you can either choose to display these results on the form - although this would be a bit of a mess if the results returns a lot of results OR you can create a report/possibly output it to word then from here your users could edit the new fields?

[garfieldsevilla]

The master table has 20 fields and the form uses 6 fields from this table.

One of the 6 fields is used to filter the master table based on a dropdown option on the form

Only 3 of the 6 are displayed, the filter field is only used to filter, and the remaining 2 are exported to a txt file along with the two temporary fields created in the form.

The user needs to review the values displayed, edit the two additional values where necessary and the a VBA routine exports the 4 fields (2 invisible fields + the 2 temporary ones) to a txt file.

The txt file must follow a strict fixed field format to be read correctly by the other system, so having the user edit this will almost certainly generate errors.

I think I can see how to program the query but I dont know how to add the two temporary fields to the query or see where the data would be stored without a temporary table.

[hedges98]

So when you filter the table, are the filtered results displayed on the form at present? How? As a datasheet? I'm confused as to how users view the filtered information...

Also, I think you're right about not being able to create the two new fields in a query without doing a make-table query (which I have no experience of) and I think what you have basically does the same thing anyway.