Need help value look up based on a control 1

[pryest]

For some of you I am sure this is a simple fix. For me... lets just say I am going bald over it.

I am working on a database to log issues by contactor by construction locations. What I have is a combo box for the contractor that filters the data so you only see issue for one contactor at a time. Contractor list is pulled from table 1 and once on the form is stored in table 3. In the “details” the form I want to then have a combo box that the user can select the location ID. When the location is selected I would like for the City and State to automatically fill in to save time. And that is where I am having problems. I have the first two combo boxes working just fine. I just can’t get the City and State to fill in. The location data pulls from Table 2 and once entered in the form it will be stored in table 3 for reporting purposes. I just can’t get it work and it is driving me nuts. I am not very experienced with Access, especially when it comes to forms.

Table 1 Contractors (has a relationship to table 3 by contactor name)
Table 2 Locations (has a relationship to table 3 by the location ID)
Table 3 Issues

Thanks

Mike

 

[fredka]

I am not crystal clear on your exact set-up. You would need a table that holds the location id along with the city and state (and whatever other fields associate to the location id)

You can then use the after update event of the location id - use the dlookup function to populate the city and state.

Although, from a design standpoint, I am not sure if you want to save the city and state in each detail record.

Fred

 

[randy700]

You might get some help by searching the FAQ's here for populating a combo box based on the contents of another combo box.


Randy

 

[pryest]

I have table 2 that has all the data i need to see. I just want it to populate the city and state when the location ID is selected.

 

[TheAceMan1]

How are ya pryest . . .

In parallel with [blue]fredka[/blue] can't tell if this is a single form or form with subform.

As a first shot at this try this in the [blue]AfterUpdate[/blue] event of combobox LocationID:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT City, State " & _
         "FROM [purple][b][i]Table2Name[/i][/b][/purple] " & _
         "WHERE ([LocationID] = '" & Me![LoationID] & "');"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      MsgBox "Location Not Found!"
   Else
      Me!City = rst!City
      Me!State = rst!State
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

If [blue]LocationID is numeric[/blue] remove the two single quotes. Also [blue]double-check spelling of field/control names[/blue] throughout.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[pryest]

Wow Thanks so much guys. It works perfectly now.