Second Value automatically determined by first

[SBelyea]

Hey everyone -

I've taken multiple stabs at this problem over the past week, but still can't seem to figure it out.

Within my database, I have a field for the name of the drainage basin a BMP (bmpDrainageBasin) uses, as well as the name of the HUC used by the drainage basin (bmpHUC). Because the HUC codes are very cryptic, I am trying to create a situation where selecting the drainage basin also automatically chooses the proper bmpHUC value. I currently have both bmpHUC and bmpDrainageBasin within the same table, but I also tried creating two different tables and linking them with Primary/Foreign Keys - without any luck. I'd greatly appreciate any help you guys and gals could provide.

Here is the layout

++ tblBMP ++
AutoIDBMPs (PK)
bmpType
AutoIDProperty (FK to another table)
bmpDrainageBasin
bmpAcreage
bmpRiserDiameter
bmpTopRiserElement
bmpDewateringElement
bmpBarrelDiameter
bmpESL
bmpESW
bmpWSD
bmpNotes
bmpHUC

Thanks in advance! If anyone solves this, you deserve a star!

 

[Remou]

Have you considered DlookUp rather than a combobox? It appears that selecting a drainage basin will return a single HUC value, if this is true, DlookUp in code with a bound textbox should suit quite well.

 

[SeeThru]

Not sure where you are going with this, but a couple of things to try:

Can you link to a table using a primary key - i.e. a seprate table of bmpDrainageBasin and bmpHUC values?

Can you use dlookup to return the last used value?

dlookup("bmpHUC","tblBMP","bmpDrainageBasin='" & me![selecteddrainagebaisin] & "'"



SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[SBelyea]

Hey guys - thanks for the help. I'm currently reading up on dlookup and seeing how I can implement it.

 

[SBelyea]

I'm working on solving this problem with Dlookup, but I'm still having a bit of difficulty with it. Here is my current VB code - both bmpDrainageBasin and bmpHUC are bound fields to tblbmp.

Private Sub bmpDrainagebasin_AfterUpdate()

    stHUC = DLookup("[huc_name]", "[tbl_creek_huc]", "[tblbmp].[bmpDrainageBasin]='" & [creek_name] & "'")
    
    [tblbmp].[bmpHUC] = stHUC

End Sub

The huc_name and creek_name are both from tbl_creek_huc, while bmpHUC and bmpDrainageBasin are from tblbmp.

Any thoughts on how to get this to work? My goal is still the same - have bmpHUC automatically filled based on what is selected in bmpDrainageBasin, and have the information stored in a table.

Thanks!

 

[Remou]

Look at it as a form thing:

[tt]Me.txtHUC=DlookUp("huc_name","tbl_creek_huc","creek_name='" & Me.cboDrainageBasin & "'")[/tt]

It may be a little more complicated than that, but I am not quite clear on your table set-up. Do not forget that you can also DlookUp a query.