Form - Query (1 Viewer)

[dudezzz]

Folks, I am a newbie in Access programming and I tried searching in this forum before posting this question. I finally gave up and am posting this here. Please help me find a solution to my requirement below:

I have a form called "frmplan" that has about 15 fields. I have an auto id field in this form whose record source is coming from "tblmaster". Based on this autoid field I need to fill out the value for the other 14 fields (I am using dlookup statement on each of the field Control Source now which is not working well) from 4 other tables in this database.

I thought using a query would be a good method, but I dont know how. What is the easiest way to get this done?

Thanks for all your help!

I have 2 other requirements on this same form - But I will bring it up as soon as I implement a solution for this requirement above.

 

[jimbrooking]

You might check Access online help under Subforms. A subform whose underlying dataset is tblmaster (with parent and child fields the same auto id field) would change every time the field changed on the parent form.

DLookups are VERY resource-intensive, and should be avoided whenever possible, lest you have to measure your response time with a calendar instead of a stopwatch.

Jim

 

[dudezzz]

subforms?

Thanks for your reply Jim.

I agree with you on the dlookup statement. It takes a looooong time to process these fields that I have. Now, comes another problem. Lets say I have the autoid field on the main form "frmplan" and I include a subform that contains 14 other fields based on the autoid field. I can display them on the subform.

Would I be able to modify these 14 fields and click on the Record Navigator button at the bottom and move to the next record (after saving the modified version to the corresponding table)?

Note: I have 4 fields from table1, 4 fields from table 2 and 6 fields from table 3. If I modify any of these fields -- I want the field values to get stored on the respective tables.

I am confused! Any help would be appreciated.

 

[jimbrooking]

Yes, you'd be able to modify any field in any table associated with the form. But you need to think about making up a query that brings all the fields together for the main form (or else different subforms, each of which covers one of the tables). If it's a query make sure it's "updateable" (see Help).

It's hard for me to see exactly what you're trying to do, but if you have a control (text box, combo box, etc.) tied to a table field, and you change the form, the value in the table will be updated automagically. No need for a special "save this" command.

Re the subform - it, too, can have navigation buttons at the bottom, but if you tie it to the main form using parent-child fields, these buttons are meaningless, and shouldn't be shown.

Bedtime in the eastern US!

Jim

 

[dudezzz]

Subforms - still slow!

Thank you. You have given me a good direction to start.

I tried using 4 subforms to accomplish what I am trying to do. But this is even slower than what I had before. Obviously, I am doing something wrong.

I have other questions on this database based on your reply as far as how I can proceed now.

Is there a way I can send you this database and explain this better?

I would appreciate if you can pass me your email id.

thanks,
Ravi

 

[jimbrooking]

Ravi,

You can click on my name in any post and "Send a private message". Should be able to include an attachment. Be sure to Compact and Repair, and ZIP the database before uploading it. If you don't have a ZIP/UNZIP tool, my favorite is FreeZip.

Jim

 

[dudezzz]

I am sending my file.

Thank you for your kindness. I am sending you my file. After I get the solution from you, I will make sure I post it here so everyone benefits from the same.

Thanks again

Ravi

 

[dudezzz]

unable to send attachment....

Jim,

I am unable to send my db to you ....it doesnt look like I can attach to this forum (or I dont have that access yet?)

Ravi