Subform Filtering

[access8]

Hello

I have a small problem. I have three Access forms one is the main form(frmAgreements) which has two command buttons. Each command button opens a form which is linked by the field AgreementNo. One of the forms (frmTasks) works great when the button is clicked it only displays the records pertaining to the Agreement No selected. The second form (frmInvoices) however does something quite funky. The second form has a subform. When I am in the main form (frmAgreements)and I click the button to go to the second form(frmInvoices) it does filter the records correctly on both the frmInvoices and subform, but I want the main field of the subform which is the InvoiceNo to display automatically. I went to design/view of the subform then I went to default view and put [Forms]![frmInvoices]![InvoiceNo] and then when I go back to the Invoice Form it tells me it cannot perform action at this time. Then it freezes the whole db. Any suggestions? Thanks you

 

[scriverb]

When you say. . .

but I want the main field of the subform which is the InvoiceNo to display automatically

do you mean you want the focus(cursor) to be set to the first record in the subform and in the InvoiceNo control?

Post back with an explanation.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[access8]

What I want is for the InvoiceNo field to display automatically on the subform to demonstrate here is a copy of how the subform looks on the main form it is in datasheet view.

InvoiceNumber DateLogged Comments
0135630 4/25/2003 12:11:00 PM Will not pay.
5/19/2004 3:01:00 PM test

Notice the second comment the invoice number does not appear automatically. I at first went to the design view of the subform and input in the default value of the InvoiceNo field [Forms]![frmInvoices]![InvoiceNo] and when I go back to the main form (frmInvoices) it gives me the error of not being able to perform the action and at the same time it stops other forms from working. Hopefully this explanation will help.

 

[access8]

I forgot to mention this is an access project in which the back end is sql server.

 

[scriverb]

If I can make a suggestion. I see this design you are using quite a bit here at TT. Mainform, button click, open up another form to display the related many records in a subform. There really is no need to go to another form. What you have here is a Main Form with the table Agreements. It is related and linked by AgreementNo to table Tasks and Invoices. A very simple design is to create multiple pages of the same form. When you want to see the Task for a specific agreement the click of a button pages down to a subform with the related tasks. The subform control properties Child link field and Master link field will automatically bring up the related records. The same holds true with the Invoices. There really is no need to open these other forms.

I can help you with this design if you would like. The second subform form is not displaying the Invoice Number. It shouldn't at this point as the rows/records should be already existing. Now the new record row has a blank InvoiceNo which we can prefill for you if that is what you want.

Let me know what you think and if I am starting to understand you problem.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[access8]

Quote:
Now the new record row has a blank InvoiceNo which we can prefill for you if that is what you want.


Yes this is exactly what I need I am very sure it will be bothersome for the user to input the Invoice No instead of it displaying automatically. Reason why I ask is because I am converting all the databases to SQL Server and the setup is different from regular Access databases. In the Access databases the InvoiceNo is shown automatically even if it is blank. Let's just say the user utilizing this db is spoiled (he he) and if I leave the subform as it is (that is user has to input the InvoiceNo manually) I will be asked either by him or my supervisor to have that fixed. Any other suggestions I greatly appreciate. )

 

[scriverb]

I don't think that this has anything to do with the fact that the BE tables are in a SQL Server database. Open up the subform in design view. Delete out the reference to the form control in the Agreemtents form. Save it. Now place you cursor back in the Default value for the InvoiceNo on the subform. Click the builder button (...) and select the forms folder on the left. Select the Agreement form and then select the InvoiceNo control and click the paste button. Click OK. The reason I have done this is that very rarely but it does happen the reference to a control does not necessary take. So, give this a try.

Also, make sure that the command button code calling this subform does not subsequently close the Agreements form. That form must be open with a value in the control for this to work.

Get back with me if this doesn't work. There is another way to do this but let's see if we can get this to work. I have created a scenario here just as you described and it works just fine.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[access8]

I tried going to the build button but it is grayed out. I think I am better demonstrating this than explaining it. Here is how this works:
All forms are linked by the AgreementNo field

MAIN FORM: frmAgreements
Button One: frmTasks (linked by AgreementNo field)
Button Two: frmInvoices(linked by AgreementNo field)

frmInvoices has a subform (subfrmComments) which is linked by the InvoiceNo field.

Mission: Have the InvoiceNo field in the subform display the current Invoice Number automatically.

I followed the instructions you sent but it did not work. you mentioned another way?

 

[scriverb]

It works here but let's drop back 10 and throw to the alternate receiver. . Okay??

Create a Global variable and a Function in a database module.

Global lngInvoice_No as Long
Public Function Invoice_No() as Long
Invoice_No = lngInvoiceNo
End Function

No in the OnActive event procedure of your fmrAgreements put the following code:

lngInvoice_No = Me![InvoiceNo]

Lastly, put the following in the Default Value for the subform control InvoiceNo:

Invoice_No()

This should finish it for you. The global variable gets loaded with the current invoice number when a new record is selected in the frmAgreements. When you open the form frmInvoices the new record row should have the invoice number from the current record in the main form.

Let me know how this technique works.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[access8]

I am not sure if I am doing something wrong but when I open the frmInvoices where the subform is placed a 0 is shown on all records. :S

 

[scriverb]

I am sorry change the code from the On Activate to the On Current event procedure of the form frmAgreements.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[access8]

It is giving me an error of "Invalid Use of Null". Hmm weird....

 

[scriverb]

When you frmAgreements opens is there an InvoiceNo in the control? Or is the error occuring prior to opening? Is the error occuring and then a line of code being displayed? Which one.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[access8]

Quote:
When you frmAgreements opens is there an InvoiceNo in the control? Or is the error occuring prior to opening? Is the error occuring and then a line of code being displayed? Which one.

Not in the frmAgreements, the InvoiceNo field is in the frmInvoices which is linked to the subform by this field. I went ahead put the code on the On Current Event but it still gives me the Invalid use of Null error. Could it be something wrong that I am doing in the module? Thank you

 

[access8]

I'm sorry I forgot to add that when I do that and I open the frmInvoices that is when it gives me the error "Invalid use of null".

 

[scriverb]

Okay. In the Invoices subform you have all the invoices for the particular Agreement. Each invoiceNO showing in the Invoices subform is differennt correct? You can't put a number into the next record InvoiceNo control because we don't know what it is going to be. How do you normally determine what the next invoice number is when you create a new invoice? Are you planning on adding invoices in the subform control? If not we can elminate that New Record row from the subform layout by changing the subform property Allow Additions to NO. That row will now disappear.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[access8]

when I create a new record in the frmInvoices the InvoiceNo field in the subfrmComments appears automatically. In the old db, I used [Forms]![frmInvoices]![InvoiceNo] in the default value of the InvoiceNo field but here with the new db it does not allow me it gives me an error of "cannot perform action at this time". This subform is used to input comments for the Invoice in the frmInvoices form. I hope this helps.

 

[scriverb]

I have to leave now but will post back in a couple of hours. I now understand. Sorry about the thick headedness. One of those days. We can get this done now.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[access8]

Cool. Thank you very much for your help the information you have provided has been helping me little by little. Hopefully by the end of this month I can get this puppy(database) to run as it should.
Thank you again

 

[scriverb]

Remove the code I provided from the Agreements form, global variables and functions. We can just use a DLookup function in the Default Value property of the subforms InvoiceNo control.

DLookUp("InvoiceNo", "Invoices", "[AgreementNo] = " = FORMS![frmAgreements]![AgreementNo])

No make sure that the Field names and control names are correct here. The first argument("InvoiceNo") is the Invoice Number field in the Invoices table. The second is the actual name of the Invoices table. The third argument is the criteria expression. "[AgreementNo]" is the Invoices table field that links back to the Agreements table. Make sure the spelling is correct. Lastly the expression is setup for a numeric comparison. If the field AgreementNo in the Invoices table is numeric then the expression will work as coded. If this is a text field however then we must modify the code as follows:

DLookUp("InvoiceNo", "Invoices", "[AgreementNo] = '" = FORMS![frmAgreements]![AgreementNo] "'")

This should get us a lot closer to a solution. Post back with results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]