How to open form depending on field in subform

[mixalisaspr]

I have a Table table_clients and a connected table (1-->many) table_invoices.

I have two forms:

form1(table_clients): all clients with subform1(table_invoices): all invoices for every client

the form2 and subform2 are the same, just objects are placed differently on page.

I want when I have opened a record on form1/subform1, to be able to click a button and open form2/subform2 filtered by the invoice_id and the client_id.

Thanks!

 

[pbaldy]

How about

http://www.baldyweb.com/wherecondition.htm

 

[mixalisaspr]

Thanks for your reply.

As I understand this link describes how to open second form based on criteria on first form.

But I want based on criteria both of subform and form.

(Or I dont understand well. I am not good with VBA)

 

[pbaldy]

The link demonstrates the method, but the criteria could come from anywhere. I would have thought that invoice ID was a unique field and thus all that was required.

 

[mixalisaspr]

I made the following.

DoCmd.OpenForm "form2", , , "form2.client_id = '" & Me.client_id & "'" AND "form2.subform2.invoice_id = '" & Me.invoice_id & "'"

1. Instead of Me.client_id it should say something like BeforeMe.client_id, because we suppose that the button is on subform1.

2. Is "AND" correct?

3. I suppose I have to put it somewhere as event to the button.

 

[pbaldy]

This should help with the subform syntax:

http://access.mvps.org/access/forms/frm0031.htm

You don't want the double quotes on either side of AND. This is where code goes:

http://www.baldyweb.com/FirstVBA.htm

 

[mixalisaspr]

That's my modified code:

DoCmd.OpenForm "form2", , , "form2.client_id = '" & Me.Parent.client_id & "' AND form2.subform2.invoice_id = '" & Me.invoice_id & "'"

but I get the following error:

Run-time error '3075':

Syntax error (missing operator) in query expression 'form2.client_id='4' AND form2.subform2.invoice_id = '123".

I probably didn't do it correctly with AND

 

[pbaldy]

Both fields are text? Try

DoCmd.OpenForm "form2", , , "client_id = '" & Me.Parent.client_id & "' AND invoice_id = '" & Me.invoice_id & "'"

 

[mixalisaspr]

Oops sorry, only the second one is text. So I put it

DoCmd.OpenForm "form2", , , "client_id = " & Me.Parent.client_id & " AND invoice_id = '" & Me.invoice_id & "'"

and when I click the button it popups:

Enter Parameter Value (for invoice_id)

Whatever I put I get:

Run-time error '3071':
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variable.

 

[pbaldy]

Can you post the db here?

 

[mixalisaspr]

I changed the command to do the association only with ids (instead of invoice id to use the record id) and I dont get the error, but still not working. Because it is bit messy check also the screenshots. Thanks again for your patience! (database is 4MB thats why I used wetransfer)

STOIXEIA.id <--> TIMOLOGIA.ids (=clients_id)
TIMOLOGIA.idt (=invoice_id)

http://we.tl/7WUaMEMliA

 

[pbaldy]

See if this lets you post it here:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[mixalisaspr]

Here it is!
View attachment new.zip

 

[mixalisaspr]

Hi pbadly,

did you find anything?