how refernce a value in a field to a table and populate another field with a value

[evanark]

Hey all, I am having a problem figuring this out. I am developing and database app that house numbers for frequency pattern. I have a form with a subform in datasheet view. The two forms are form two separate tables linked via accountID. My problem is I have this field called network number. When enter the a network number, I want the field called hop 1 to autopopulate form a table call hoppingPatterns. The hoppingpattern table has list of numbers that are already in the table and they will not change. this table is just for holding these numbers

tblHoppingPattern
PK hopID
networkID
hopnumber
When the user enters a in the field network number I want the hop 1 in the subform to populate base on the networkID.


example network number is will be stored in the table Systemconfig but will reference networkId in the HoppingPattern table.

in the HoppingPattern table NetworkID 30 has a hop number of 23

So if the user enters 30 in the subform field network number it see that 30 in the hopping pattern table NetworkID field and assigns the subform field hop 1 the value 23 from the HoppingPattern table hop 1 field. I am not sure how to do this. I apologize if this in confusing but I know what want it to do but have a hard time explaining it. Can any one advise me?

 

[jdraw]

Can you show us your tables and relationships as a jpg?
My first reaction is that you are deeply into Access constructs - queries, forms, subforms etc, and you may have a table design issue. Note I said MAY.
Your post doesn't clearly separate the WHAT you are trying to do from the HOW. I'm sure once you clarify WHAT, then readers will have suggestions.
Good luck.

 

[evanark]

Here is my table layout.I also have sample data in thetblHoppingPattern table, this table is just a reference table that has the values already in it and those values will not change. On the subform is connected to the tblSystemConfiguration. The field sysNetworkNumber on the subform will have an after update event that will populate the field name Hop 1 on the subform. The value is to be automatically populated in the Hop 1 field by the after update event after the user enters a number in the sysNetworkNumber field in the subform. What I want is for the code to find the matching value the user entered in the sysNetworkNumber field to the hpNetworkID in the tblHoppingPattern. Once the match is found, it will pull the related hpHop1 value from the reference table tblHoppingPattern and populate the field in the subform named Hop 1. I can do this with with a Select Case code, but it would take alot of time. There are 64 NetworkID and each on has a Hop1 number value related to it. I was wondering if you can pull from a table. I know its hard to explain and I apologize for the confusion. Note hpNetworkID starts with the value 0 so I had to create a primay key hpHopID to link the table.

 

[evanark]

I figured it out!! I need to use Dlookup but my syntax was wrong. All is good