Combobox values to table

[beerdini]

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.

[Welshgasman]

Put your sql string into a string variable and debug.print that to see what you have not what you think you have.
You are at least missing a . before Column

[orange]

Can you provide a few examples (can be dummy/mock up data) for each of your tables to provide some context?

Your SQL is not being executed if your Private Sub cmdSubmit_Click() is complete as shown. The procedure also has syntax issues.

[June7]

SQL = "INSERT INTO tblClientRule (tblIPID, tblPolicyID) " & _
         "VALUES (" & Me.cboIP.Column(0) & ", " & Me.cboPolicy.Column(0));"

Show code that actually executes the SQL statement.

But why use VBA and not simply bind form and controls?

[Cottonshirt]

SQL is a reserved word.


good luck with your project.


Cottonshirt

[June7]

Yes, SQL is reserved word for JET, ODBC, ANSI-92 but VBA has no problem with it declared as a variable. However, could play it safe and use strSQL instead.

[orange]

Awaiting some response from beerdini.

[beerdini]

Awaiting some response from beerdini.

Here are some images of sample data, relationship structure, code and the input box that I'm making. I apologize about image quality, it triggers exfiltration alarms if I try to send to myself so had to do the pic route.

https://u.pcloud.link/publink/show?c...BTz8BsYp1J6DYk

I added the Debug.print command that someone suggested and I'm not seeing any difference when I click the submit button.

Since I haven't really developed in Access in a long time my relationship structure might not be usable. In the system I'm trying to map and report I have 2 types of users, IP and actual user, and 2 types of rules, policies and exceptions. An IP or User can have multiple policies or rules assigned to them which is why I'm trying to go for the format that I'm using for tblClientRule. I'm trying to be ambitious and use a single table rather IP/Policy, IP/Exception, User/Policy, User/Exception, since ultimately I am planning to build this out to which URLs are managed by each policy and exception. Again each URL can be used in multiple policies or exceptions.

So what I'm trying to do right now is write the values of the ID fields in the IP and Policy tables to the 1st and 3rd columns in the sample data that I provided, the others can remain null, if that is permissible by access rules. Being able to do this is really the foundation of me being able to associate the IPs, Users, Policies, Exceptions and URLs with each other and once I have this down I'll be busy with linking the data.

[pbaldy]

Post 8 was moderated, I'm posting to trigger email notifications.

[June7]

Did you see post #4?

[mike60smart]

Hi

Your INSERT statement is missing the following line after the Debug.Print strSQL line :-

CurrentDb.Execute strSQL, dbFailOnError