Update Field based on multiple combo boxes selections 1

[FishPo]

I'm having trouble implementing this in an invoice.

Basically, I am trying to select a:
1. Company
2. Contractor
3. Service

And have the rate automatically updated in the invoice_details subform.

The Company and Contractor are located in the invoice form.

The Service, Quantity, and Rate are located in the invoice_details subform.

I've setup another table and form which handles all the rates. It's independant from the invoices table, and contains:
Company, Contractor, Service, Rate

Company, Contractor, and Service all lookup to their respective tables to grab their stuff.

How can I get the rates field in the invoice_details to automatically update based upon the three selections in the combo box, and to display an error message if a rate does not exist?

Thanks.

 

[GingerR]

say in your combo box for Service (i'll call it "cboService"), you have the RATE column included in the RowSource. Says it's column 3 (If you don't want to see it when the user clicks the combo box, just make it's width 0). You can you can set the RATE text box to be (column numbering starts at 0):

=IIf(cboService.column(2)=Null Or cboService.column(2)="","BAD",cboService.column(2))

So if Rate is null or "", a message will go in there ("BAD"). Otherwise, it'll put the rate in there.

Hope this helps--g

 

[FishPo]

Thanks for the quick response, and unfortunately, that's only a small piece.

The rate column should also be defined by the selection in the contractor and company drop-down boxes which are located in the form.

 

[GingerR]

but isn't the SERVICE related to the COMPANY and CONTRACTOR before it's even selected?

i thought this is what is happening:
you have a table that has Contractor, Company, Service and Rate in it.

On your form, you pick a Company. Based on what is in the Company combo box, you pick a Contractor. Then based on which Company and COntractor are picked, you pick a SERVICE from the combo box. What i mean is that the rowsource of the Contractor combo box is based on which company is picked in the Company combo box....then the Service combo box rowsource is based what you've picked in the Contractor combo box....therefore I was thinking that the Company, Contractor and Service would be pointing to a specific record in the table which has the Company, COntractor, Service and Rate in it?

 

[FishPo]

The Company, Contractor and Service options are all in different tables.

So..on the form, the companyid is looked up from its own table.

Then, the contractor is selected from his own table.

And the service from its own, so each selection is independant of one another.


Here's the table list.

------
Site
-siteid
-locationname
-location


Contractor
-contractorid
-firstname
-lastname
-etc etc

Invoices
-invoiceid
-date
-contractorid (lookup)
-companyid (lookup)

InvoiceDetails
-invoiceid
-serviceid (lookup)
-quantity
-rate

Rates
-rateid
-companyid
-contractorid
-serviceid
-rate

Company
-companyid
-name
-address
-etc etc
-------

So the main form contains the details of the site.
Then there's a subform which has the main invoice (contractor and company) and then inside that subform is another subform containing the invoice details (service, quantity, rate)

Hopefully that explains it better.

 

[GingerR]

what's the rowsource for your Service combo box?

 

[FishPo]

Oops, forgot a table.

Services_tbl

ServiceID
Service
Unit Type

 

[GingerR]

so what if someone chooses a service that isn't provided by the company or contractor?

 

[FishPo]

Then it'll be a unique circumstance and the rate can be entered manually.

Currently, we have a table on paper which lists each company, each service, and then the rate to charge each at.

So, there should be a service for all pairings of contractor and company.

Otherwise, the rates table will have to be revised if the service is commonly used.

 

[TheAceMan1]

How are FishPo . . . .

[blue]If my read is correct, you want to [purple]lookup the compound criteria, consisting of the values selected in Company & Contractor & Service on the Invoice form/subform [/purple] in the rates table and [purple]return the matching value to the Rate textbox (if found)[/purple] on the Invoice_Details subform.

Is this correct?[/blue]

Can Do! . . . except, need to know how selections are made in the Invoice Forms. It appears [blue]Company & Contractor, on the Invoice form[/blue] and [purple]Service on the Invoice_Details subForm[/purple] are lookup combos, [blue]while Rate on the Invoice_Details subform is a textbox.[/blue] Is this correct?

The bound columns (the IDs) of the lookup combos above are [blue]Numeric[/blue] or [blue]Text[/blue]?

Is it possible for [blue]any combination[/blue] of Company, Contractor, and Service to [blue]have more that one record[/blue] in the rates table?

Post the [blue]actual names[/blue] of the Invoice form/subform and the controls of interest on these forms.

See Ya! . . . . . .

 

[GingerR]

Here's what I'm assuming; you can tweak as necessary:

all ID's are numbers
Main form name = "Main"
subform name = "frmRateSub"
RATE text box name = "txtRate"
combo boxes are named cbo...


Make a new module, and paste in this function. It will get the rate depending on your selections. It will also return null if any of the combo boxes have no selection, and will return "NO MATCH" if your selections yield no match:

    Function GetRate(CompanyID, ContractorID, ServiceID)
    'If any of the values are null (no selection in combo box) then return null
    If IsNull(CompanyID) Or IsNull(ContractorID) Or IsNull(ServiceID) Then
        GetRate = Null
        Exit Function
    End If
    'Otherwise, go get the Rate from the Rates table
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "Select * from Rates where CompanyID = " & CompanyID & " and ContractorID = " & ContractorID & " and ServiceID = " & ServiceID
  

    Set rs = CurrentDb.OpenRecordset(strSQL)
    If rs.EOF And rs.BOF Then 'there is no match
        GetRate = "NO MATCH"
    Else
        GetRate = rs!Rate
    End If

    Set rs = Nothing

End Function

To call the function, put in the Control Source of your txtRate text box:

=getrate(Forms!Main!cboCompanyID,Forms!Main!cboContractorID,[cboServiceID])

Then in order to get the rate to update whenever you change the Company, Contractor or Service:

put in the AfterUpdate event of each of the Contractor and Company combo boxes:

Me.frmRateSub.Form.txtRate.Requery

and in the AfterUpdate event of the SupplierID combo box:

Me.txtRate.Requery

Because this is a function, you could later call it from other parts of your db, just by passing the variables CompanyID, ContractorID, and ServiceID.

Hope this helps, or at least points you in the right direction.

g

 

[FishPo]

Wow thanks so much Ginger, I'll give this a shot, and see how it goes.

Aceman:

Company, Contractor, and Service should only have ONE record per combination.

I'll see how ginger's suggestion works out, thanks for the reply though.

 

[FishPo]

I'm having a small problem..

When calling the function after selecting a service.

I get a compile error:
User-defined type not defined

And the top line is in yellow;

'Function GetRate(CompanyID, ContractorID, ServiceID)'

and this line is highlighted: 'rs As DAO.Recordset'

 

[GingerR]

make sure a reference to DAO is set:

with a module open, from the toolbar pick TOOLS+REFERENCES. scroll down to find Microsoft DAO 3.6 blah blah blah.

check that box. try again.

 

[FishPo]

Perfect, works wonderfully.

Thanks so much Ginger.