If, Else statement with current record

[Bentleybelle]

I started a thread on this yesterday, but realised I was some way off achieving what I want, so am starting a new thread.

Have attached copy of my db. What I want to achieve is this: From frmLayClientDetails click on the cmd button See invoice details, then click on the cmd button Preview this invoice. I want to see just the invoice for the current record – easy enough for most, and I have seen code for this elsewhere in the forum. However, clients have different invoicing addresses, depending whether they are dealt with directly (Private) or via another consultancy, hence I have two invoice layouts (OK, I’m sure there is a way of getting around this as well). So as the db is at the moment, I need to say “If the SolicitorID is 1, then open rptInvoicePrivateClients, Else open rptInvoiceLocumWork”

Can someone help me with this please?
Thanks in anticipation.

 

[gemma-the-husky]

doesn't that work ok, as you described it

in the onclick event of the button ...

If SolicitorID is 1 then
docmd.openreport "rptInvoicePrivateClients",acviewpreview
Else
docmd.openreport "rptInvoiceLocumWork",acviewpreview
end if

 

[Bentleybelle]

No, this doesn't work. Don't I have to declare a variable? This is the bit that I am not sure of.

 

[Rich]

If SolicitorID = 1 then
etc

 

[Bentleybelle]

I am using this bit of code, but I am sure I am missing other coding. This is what I have currently:

Private Sub Print_Click()
On Error GoTo Err_Print_Click

Dim stInvPriv
Dim stInvLocum


stInvPriv = "rptInvoicePrivateClients"
stInvLocum = "rptInvoiceLocumWork"

If SolicitorID = 1 Then
DoCmd.OpenReport stInvPriv, acViewPreview
Else
DoCmd.OpenReport stInvLocum, acViewPreview
End If

Exit_Print_Click:
Exit Sub

The table holding the data is tblLayclientDetails (this is where I select the SolicitorID).

I input client data on frmLayoutClientDetails. On this form is a cmd button opening frmInvoiceEntry. It is on frmInvoiceEntry I have the cmdbutton that I want to print the current record (and I have seen reference to this elsewhere in the forum specifying the ID in the query) but it has to choose the correct invoice layout.

Help would be appreciated.

 

[gemma-the-husky]

silly of me, i meant

If SolicitorID = 1 then
docmd.openreport "rptInvoicePrivateClients",acviewpreview
Else
docmd.openreport "rptInvoiceLocumWork",acviewpreview
end if

however, solictorid needs to be a field name on the current form in order for this to work. if you are getting an error then i presume this is not the case.

I assume also that the query driving the report will only produce an invoice for the current record - not all the records - you need some sort of filtering system in the report query.

 

[Bentleybelle]

I did not have the solicitorID on the form because I guessed you could refer to it (and that was really the bit I was not sure about!!) but I have put it on the main form (rather than the sub form) as a combo box and put it in as a field in tblInvoices - though this is duplication isn't it?

However, it now works!! So many thanks for that.

(I haven't quite finished with this, so might be back!)

Many thanks - I have learnt a bit more about vba

 

[gemma-the-husky]

ive not looked at your dsbs, but generally (if i understand you correctly) -

if you have a table of solicitors and a table of invoices, and you need the tables to have a common link, then using solicitorid to provide this common link is not duplication - this is just the way to accomplish that process - ie indicating that an invoice "belongs" to a given solicitor.