By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Temporary fields in a query

P: 37
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..
Feb 23 '10 #1
Share this Question
Share on Google+
5 Replies


100+
P: 109
I'm probably wrong but, couldn't you do this using queries/subqueries?
Feb 23 '10 #2

P: 37
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.
Feb 23 '10 #3

100+
P: 109
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?
Feb 23 '10 #4

P: 37
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.
Feb 23 '10 #5

100+
P: 109
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.
Feb 23 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.