I haven't used Access in almost 20 years but am trying to use the tools available to me for trying to build a database to help me search the details of a web filter that my company uses because the vendor doesn't have a good system to make searchable reports based on entering a client and finding all of the rules associated with it. This is a system that has been in place 10+ years, many hands in it over the years and many duplicate rules, etc... that need to be cleaned up. I'm able to export from the web filter and import into Access tables, now I'm trying to make the join tables so I can associate items, but after reading a bunch of posts and videos I can't figure out how to get things to write to the joining table. My tables are as follows:
tblIP
-ID (PK)
-FirstIP
-LastIP
tblClientRule
-tblIPID
-tblPolicyID
- 2 other ID's for tables not yet associated and null values are acceptable
tblPolicies
-ID (PK)
-policyName
I have created a form with:
3 column combo box based on tblIP. ID is hidden, FirstIP shown, LastIP hidden but displayed in a text box next to combo
2 column combo box based on tblPolicies. ID is hidden, policyName is shown
I want to select the IP address/range in the first combo box, the policy in the 2nd combo box, and click a submit button to write the hidden ID values to their associated value in tblClientRule. I've tried several different versions of _Click on the box but so far nothing gets written to the table. I've tried several different variations but the latest I tried the following:
Private Sub cmdSubmit_Click()
Dim SQL as string
SQL = "INSERT INTO tblClientRule (tblIPID,tblPolicyID) &_
VALUES (Me![cboIP]Column(0),Me![cboPolicy]Column(0));"
Again, the intent is to have the ID's from tblIP and tblPolicies to be written to the associated field in tblClientRule. This will allow me to do future reporting where I can query an IP address and see all of the policies that have been assigned to that IP, or search the opposite. Right now I click the button, nothing changes, no record update at the bottom of my form and nothing else added to the table.