field value based on other fields

[gmmccarthy]

Here is what I am trying to do.

I have a table with routes that tell me which carrier to use. This is based on from what state to what state it is traveling as well as the service lever (next day, standard, etc.)

How would I code for the value in the carrier field to populate based on the state and service level values entered.

Should I write a query to get the results? Or should it be code in an event on a particular field?

Whichever way works, any ideas on how to get started?

Thanks.

Gary

 

[gromit]

Hi Gary -

First of all, how would *you* go about determining the correct carrier? If you can think about how you would do it, it can make it easier to figure the best implementation.

If you have a combination of look ups, then one of the methods that comes to my mind would be to have a field dependent on some combo boxes. This is often called "cascading combo boxes".

This is done by having some VBA code that triggers on the AfterUpdate event for the combo boxes.

A query might work here too, it depends a little on what you want the output to look like.

Sorry if this seems vague, what questions do you still have?

- g

 

[gmmccarthy]

I was trying to go the query route but with little success. Basically, I want to return a result from my routing guide table that would ask for the origin state, the consignee state, and the service level. The only output I need is the carrier name to be populated into the carrier field.

OriginSTfield = "TX"
ConsSTfield = "GA"
Servicefield = "Standard"

Carrierfield = "Watkins" (populated based on above choices)

I will try the cascading boxes as that sound like a logical approach. Any pointers in the code would be appreciated.

 

[gromit]

Hi Gary -

See attached db.

My basic approach is as follows -
1. Having your routing table set up with each of the fields
2. Make a query for each of the following: OriginStates, ConsigneeStates and ServiceLevel. These are each essentially just summaries of the available options. I used these for the search form to keep things clean.
3. Make a search form. I used combo boxes for Origin, Consignee and Service. These are tied to the queries.
4. A little code - the OnLoad event sets the results box to "(Make your selections)" to prompt the user
5. The search button contains the following code

Private Sub btnSearch_Click()
    
    ' Declarations
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim varWhere As Variant
    
    ' Initialize the WHERE clauses
    varWhere = Null
    
    ' Add Origin criteria
    If IsNull(Me.cboOrigin) Then
        ' Do nothing
    Else
        ' Note use of triple " marks to make the result a string literal
        varWhere = (varWhere + " AND ") & "( [OriginSTfield] = """ & Me.cboOrigin & """)"
    End If
    
    ' Add Consignee criteria
    If IsNull(Me.cboConsignee) Then
        ' Do nothing
    Else
        ' Note use of triple " marks to make the result a string literal
        varWhere = (varWhere + " AND ") & "([ConsSTfield] = """ & Me.cboConsignee & """)"
    End If
    
    ' Add service criteria
    If IsNull(Me.cboService) Then
        ' Do nothing
    Else
        ' Note use of triple " marks to make the result a string literal
        varWhere = (varWhere + " AND ") & "([Servicefield] = """ & Me.cboService & """)"
    End If
    
    strSQL = "SELECT Carrier from tblRouting " & (" WHERE " + varWhere)
    
'    Debug.Print Me.cboOrigin, Me.cboConsignee, Me.cboService
'    Debug.Print varWhere
'    Debug.Print strSQL
    
    ' Open the database
    Set dbs = DBEngine(0)(0)

    ' Check for matching record
    Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not (rs.EOF) Then
        Me.txtResult = rs!Carrier
    Else
         Me.txtResult = "No match found"
    End If

    'debug.print Me.txtResult

    ' Clean up and exit
    rs.Close
    Set rs = Nothing
    dbs.Close
    Set dbs = Nothing
    
End Sub

Not sure how much of this is familiar to you or not, so ask questions. Basically, I use the state of each combo box to build part of the search query. All of these are joined together in one SQL string. Then I query the database. If there is a match, then this is put into the results field. If no matching record is found, then let the user know.

Some final notes -
1. The search will return the FIRST record found, so if there are more than one matching records, you won't see them. A better approach would be to use a subform rather than a text box as the results, and let the SQL string set the subform recordsource.
2. The table in the attached db is roughly what I imagined that you described. Note that this is not "normalized" depending on what you are doing, you may not care about this or not. As the database grows, it might be helpful to evaluate a different table design (and probably the use of multiple tables).

- g