Requerying the data in a subform after making a selection from the main form

[Tophan]

Hi...I posted a question yesterday but I think I posted in the wrong forum which is probably why I didn't get a response. I am really desperate for help as this problem is preventing my database from working. Here it is:

Main form name= "Invoice"
Sub-Form= "Invoice Details"
Query Name= "Job Select"

In the "Invoice" main form there is a combo box "Bill To" from which I would select the client to bill. Once this selection is made, the query "Job Select" lists the jobs to which that client is attached in the field name "Job Number" on the sub-form.

This is only working on the first record. When I go to the next, previous or a new record, the "Job Number" in the sub-form re-lists the information for the first Invoice created and does not requery.

I am an absolute novice when it comes to code so I really really need some help here.

I was searching this and other forums and found someone with a similar problem who was successful using the following code

Forms![MainFormName]![subFormName].Form![controlname].Requery

which in my case I put (on the After Update event on 'Bill To' in the main form)

Forms![Invoice]![Invoice Details].Form![Job Number].Requery

but still no luck. If the code doesn't reference the query name how can it work? Did I leave something out or type it incorrectly?

I want my database to start to function and my head is hurting trying to figure this problem out....PLEASE SOMEONE HELP ME

 

[RuralGuy]

The description of your issue is a bit fuzzy for me. Is your SubForm in Continuous Form view? To what are the LinkMaster/ChildFields properties of the SubFormControl set?

 

[Tophan]

Hi,

The sub-form "Invoice Details" is a datasheet which is linked to the main form "Invoice" by the field name InvoiceNo which is just an autonumber field.

The fields in the Invoice table are

InvoiceNo - autonumber
Date - date field
Bill To - which is a lookup up field from a Client Select query

The fields in the Invoice Details sub-form are
InvoiceNo - same as InvoiceNo in Invoice table (one to many relationship)
Job No - which is a lookup up field from a Job Select query
Description - memo
Cost - Currency field

The Job No field is to reference the Client selected in the "Bill to" field in Invoice table and return only those jobs to which the client is attached using the Job Select query.

It is working in the tables and the queries but not in the form.

I mainly use macros because i simply do not know how to write code and I am trying to learn now. If you need me to clarify anything else please ask me as I will be monitoring the forum all day in the hopes of getting a solution to my problem.

If you would like to look at my database let me know and I will attach it.

Thanks for reading and I hope you can help me.

 

[RuralGuy]

Try setting your SubForm to Continuous Form mode instead of Datasheet View and see what happens.

 

[Tophan]

I'm getting a run-time error 2465.

Is the code I used correct?

 

[RuralGuy]

Go ahead and attach your zipped up db to a post after you do a Compact and Repair.

 

[Tophan]

Requerying Sub Form from Main Form Selection - Database

Please see attached database. I have deleted and re-done this now 3 times so please excuse how very basic it looks

 

[Tophan]

I just uploaded the database - please note I deleted the code I had added to the After Update event on the main form. Could you check if my relationships make sense - I have a feeling the problem may be with the relationships but I am not sure how to fix it.

thanks for your help

 

[RuralGuy]

Your db did not get attached to your post.

 

[HiTechCoach]

Re: Requerying Sub Form from Main Form Selection - Database

Please see attached database. I have deleted and re-done this now 3 times so please excuse how very basic it looks

You will probably get a better response if you give more details.

What are you trying to do?

What is the issue?

Are you getting error messages?

What are the steps to recreate the issue.


PS: Not everyone downloads attachments due to the security risk.

 

[DCrake]

Re: Requerying Sub Form from Main Form Selection - Database

This is set up correctly just add some matching records to your inoice details table and navigate you main form records.

 

[Tophan]

Hi...see attached. I had actually posted it under a new thread.

 

[Tophan]

Re: Requerying Sub Form from Main Form Selection - Database

Sorry...I had actually posted the problem under the thread "Requerying the data in a subform after making a selection from the main form" but misunderstood the previous instruction and thought I had to post the actual database under a new thread.

Please see above-mentioned topic.

 

[DCrake]

This is what happens when you double post.

 

[RuralGuy]

I'm going to start by suggesting you remove the LookupFields from the tables.
http://www.mvps.org/access/lookupfields.htm
http://www.btabdevelopment.com/ts/removelookups

 

[Tophan]

If I remove the lookup fields then I run the risk of data being keyed incorrectly or spelling errors which would create errors with any reports I need to run later on.

Is there any other way to get the form to work?

I really do appreciate your help but I am just worried that by removing the lookup fields I will run into bigger problems later on

 

[RuralGuy]

Lookup's (ComboBoxes) on a form are just fine and should be used for what you are using them, just not at the *table* level. Removing them from the table does not change your forms or how they work.

 

[Tophan]

Ok...I removed the lookup fields from the tables but the data is still not requerying in the sub-form

 

[RuralGuy]

You can correct me if I'm wrong but is seems as though your tables are not named correctly. After examining the relationships of the tables it seems as though the JobDetails table should be named Jobs and the InvoiceDetails table should be named JobsDetails and it should really have its own AutoNumber PrimaryKey field. Am I just looking at this the wrong way?

 

[Tophan]

The Job Details table refers to the specific project and links to the Clients table whereas the Invoice details would give a description of what work we are invoicing for the specific job. I could have several Invoice Details for one Job as the job will be billed on a monthly basis. I could rename the Job details table to just Jobs but I think the relationships are correct.

The relationships I can't seem to get to work are linking the queries to the tables.