1

I'm running into problems populating three tables with information from one form that run 3 lines of INSERT INTO on an On Click event in the related form.

I have a form that gets filled out and has a submit button. When the submit button is clicked the information in the form is supposed to populate 3 different tables (code provided below).

The tables are Networks, Radios, and Scans.

Networks has a primary key of SSID, which has a one to many relationship with the SSID field in the Radios table. The Radios table has a primary key of BSSID, which has a one to many relationship with the BSSID field in the Scans table. The Scans table has a primary key called index that is just an auto-number type all the other fields need to be able to accept duplicates.

When the relationships are intact, only the networks table gets filled out; but if I remove these relationships then the networks and scans table gets filled out, but the Radios table is still blank.

At this point in time I do not know what the issue is; other than to surmise it has to do with the Radios table.

Both the form field for BSSID and the BSSID field in the Radios table have a mask of:

AA:AA:AA:AA:AA:AA;

Primary keys are set to required, no zero length for BISSD, Indexed (no duplicates).

All three tables are empty, so I know there isn't a duplicates issue.

Also, if I fill in the tables in the stated order through the GUI, I have no issues.

This is the code I am using, please keep in mind of the relationships I have stated above. (Also note, if I remove all relationships the Networks and Scans tables will populate, but Radios table WILL NOT populate).

CurrentDb.Execute "INSERT INTO Networks (SSID, Network, Authentication, Encryption)" & "VALUES ('" & Me.txt_SSID & "', '" & Me.cmb_NetworkType & "', '" & Me.cmb_AuthenticationType & "', '" & Me.cmb_EncryptionType & "')"

CurrentDb.Execute "INSERT INTO Radios (BSSID, SSID, [Radio Type], Channel, [Base Rate], [Other Rate])" & "VALUES ('" & Me.txt_BSSID & "', '" & Me.txt_SSID & "', '" & Me.cmb_RadioType & "', '" & Me.txt_Channel & "', '" & Me.txt_BaseRate & "', '" & Me.txt_OtherRate & "')"

CurrentDb.Execute "INSERT INTO Scans ([Scan Date], Location, BSSID, [Signal Strength])" & "VALUES ('" & Me.cmb_ScanDate & "', '" & Me.cmb_ScanLocation & "', '" & Me.txt_BSSID & "', '" & Me.txt_SignalStrength & "')"

My expectation is for all three tables to populate a new record upon the execution of this code.

braX
  • 11,506
  • 5
  • 20
  • 33
Ken
  • 125
  • 1
  • 12
  • SSID looks like a potential culprit here too. Failure on insert for that field would give the same results as BSSID. Couple of questions. 1. Are the datatypes and masks the same for SSID in Networks and Radios, and are the datatypes and masks the same for BSSID in Radios and Scans? 2.Have you rebuilt these queries inside of query designer/sql view? You can reference form controls using Forms!YourForm!YourControl.Value to mimic the "Me.txt". Doing this will allow you to take advantage of the errors prompts you aren't getting by running this in VBA code. – Mike May 16 '19 at 17:37
  • The data types and masks match in each table as well as on the form. All are short string, and only BSSID is masked, and masks are the same. I have not used the query editor, I was building the VBS code directly. I will look into the query builder. – Ken May 16 '19 at 18:17
  • Actually, update your insert statements to include the dbFailOnError option. `CurrentDb.Execute "YourSQLString", dbFailOnError` This will give you the same prompt that the query builder will on failure. Let us know, what, if anything pops up – Mike May 16 '19 at 18:32
  • Mike - Thanks, that helped a lot, it turns out my Radio Type combo box was the culprit. The combo box is populated by a table that I use as a control for the WiFi radio types (e.g. 802.11ac, 802.11g, ect). For entering a lot of data it was getting to be a pain to select that option as oppose to, say an auto populating field. So what I did was make a mask for that entry, 802.11L?, so you only have to type 1 or 2 letters and the combo box would auto-populate with the rest. I thought because the combo box with mask displayed the correct text that what was displayed would be entered into.... – Ken May 16 '19 at 19:24
  • ...into the table field, but since I added the fail on error I saw that was not the case. Thanks so much!!!! – Ken May 16 '19 at 19:24

1 Answers1

0

As you explained, the Scans is the base table with Radios as its child and Networks as its grandchild table. Therefore, change the order of action queries with Scans first and Networks last due to the dependent fields. Also, consider parameterization with QueryDefs for cleaner, more maintainable and readable code between SQL and application layer (VBA):

SQL (save each as stored Access query)

  1. Scans Append

    PARAMETERS Scan_date_param TEXT, Scan_loc_param TEXT, BSSID_param TEXT, Signal_param TEXT;
INSERT INTO Scans ([Scan Date], Location, BSSID, [Signal Strength])
VALUES (Scan_date_param, Scan_loc_param, BSSID_param, Signal_param);

  2. Radios Append

    PARAMETERS BSSID_param TEXT, SSID_param TEXT, Radio_param TEXT,
           Channel_param TEXT, Base_rate_param TEXT, Other_rate_param TEXT;
INSERT INTO Radios (BSSID, SSID, [Radio Type], Channel, [Base Rate], [Other Rate]) 
VALUES (BSSID_param, SSID_param, Radio_param,
        Channel_param, Base_rate_param, Other_rate_param);

  3. Networks Append

    PARAMETERS SSID_param TEXT, Network_param TEXT, Auth_param TEXT, Encryp_param TEXT;
INSERT INTO Networks (SSID, Network, Authentication, Encryption)
VALUES (SSID_param, Network_param, Auth_param, Encryp_param);

VBA (assign each query and bind params, no quotes or string concatenation)

Dim qdef As QueryDef

Set qdef = CurrentDb.QueryDefs("myScansAppendQ")

qdef!Scan_date_param = Me.cmb_ScanDate
qdef!Scan_loc_param = Me.cmb_ScanLocation
qdef!BSSID_param = Me.txt_BSSID
qdef!Signal_param = Me.txt_SignalStrength

qdef.Execute dbFailOnError
Set qdef = Nothing

Set qdef = CurrentDb.QueryDefs("myRadiosAppendQ")

qdef!BSSID_param = Me.txt_BSSID
qdef!SSID_param = Me.txt_SSID
qdef!Radio_param = Me.cmb_RadioType
qdef!Channel_param = Me.txt_Channel
qdef!Base_rate_param = Me.txt_BaseRate
qdef!Other_rate_param = Me.txt_OtherRate

qdef.Execute dbFailOnError
Set qdef = Nothing

Set qdef = CurrentDb.QueryDefs("myNetworksAppendQ")

qdef!SSID_param = Me.txt_SSID
qdef!Network_param = Me.cmb_NetworkType
qdef!Auth_param = Me.cmb_AuthenticationType
qdef!Encryp_param = Me.cmb_EncryptionType

qdef.Execute dbFailOnError
Set qdef = Nothing
Parfait
  • 104,375
  • 17
  • 94
  • 125