Reference to Form fields in Update (1 Viewer)

[phinix]

Hi guys!
I have another problem

I have a two tables called "Data" and "Import" and a form called "Form_MappingTables".

In this form I have two dropdown lists: Address1 and Address2.
These are lists of fields from table "Import".

I'm trying to write a function that inserts these two fields data into Data table.
So, user will pick from dropdowns fields (Source_Address1 and Source_Address2) then clicks a button run run macro, which runs this function.
It supposed to insert data from Import.Add1 and Import.Add2 by using field names from form dropdowns.

I hope this makes sense.

I get error:"Error updating: Access database engine cannot find the input table or query 'Form_MappingTables'..."

Here is my code...

Public Function MappingForm() As Boolean
On Error Resume Next
  
Dim ws As Workspace
Dim db As DAO.Database
Dim strSQL As String
Dim tbl As String
Dim frm As String
Dim Form_MappingTables As Form


Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
tbl = "Data"
frm = "Form_MappingTables"

On Error GoTo Proc_Err
'start a transaction to ensure all updates are run or rolled back
ws.BeginTrans
    
strSQL = "INSERT INTO [" & tbl & "] ( Address1, Address2 ) SELECT [" & frm & "].Source_Address1,[" & frm & "].Source_Address2 FROM [" & frm & "]"
db.Execute strSQL, dbFailOnError


 'commit all changes
  ws.CommitTrans

Proc_Exit:
  Set ws = Nothing
  Set db = Nothing
  Exit Function

Proc_Err:
  ws.Rollback
  MsgBox "Error updating: " & Err.Description
  Resume Proc_Exit
End Function

I know I messed it up, but trying to simply use those dropdown from form to define which fields should be used to insert.

Please help!

 

[theDBguy]

Hi. You said you had two tables: Data and Import, and your error message says Access cannot find the table Form_MappingTables, which sounds correct because it's not a table but a form. I suggest doing a Debug.Print strSQL to see what your query really looks like.

 

[phinix]

Hi. You said you had two tables: Data and Import, and your error message says Access cannot find the table Form_MappingTables, which sounds correct because it's not a table but a form. I suggest doing a Debug.Print strSQL to see what your query really looks like.

Sorry, you lost me...
Do what?

I know that my SQL string is wrong as this isn't a table, but form.

What I want it to do is to look at the form, get data fields names from dropdowns and get that data from ImportTable.

I added these:

Dim tbl2 As String
tbl2 = "ImportTable"

I fixed SQL to this:

strSQL = "INSERT INTO [" & tbl & "] ( Address1, Address2 ) SELECT [" & frm & "].Source_Address1,[" & frm & "].Source_Address2 FROM [" & tbl2 & "]"

but it still giving me error
This time: "Too few parameters. Expected 2."

 

[vba_php]

I suggest doing a Debug.Print strSQL to see what your query really looks like.

put a break point on the line right after the line in ur code where "strSQL" appears, when it is paused press "Ctrl+G" to open the immediate window and write in the immediate window:

?sqlSQL

press ENTER.

 

[theDBguy]

Sorry, you lost me...
Do what?

Between strSQL=... and db.Execute..., insert a Debug.Print statement. For example

strSQL=...
Debug.Print strSQL
db.Execute strSQL,...

Then, in the Immediate Window, tell us what you see after you get the error.

 

[phinix]

OK, got it

It says:
INSERT INTO [Data] ( Address1, Address2 ) SELECT [Form_MappingTables].Source_Address1,[Form_MappingTables].Source_Address2 FROM [ImportTable]

 

[vba_php]

OK, got it

It says:
INSERT INTO [Data] ( Address1, Address2 ) SELECT [Form_MappingTables].Source_Address1,[Form_MappingTables].Source_Address2 FROM [ImportTable]

guy can take it from here...he'll help you out.

 

[theDBguy]

OK, got it

It says:
INSERT INTO [Data] ( Address1, Address2 ) SELECT [Form_MappingTables].Source_Address1,[Form_MappingTables].Source_Address2 FROM [ImportTable]

Hi. Try.

 strSQL = "INSERT INTO [" & tbl & "] ( Address1, Address2 ) SELECT [" & tbl2 & "]." & Me.Source_Address1 & ",[" & tbl2 & "]." & Me.Source_Address2 & " FROM [" & tbl2 & "]"

 

[phinix]

Hi. Try.

 strSQL = "INSERT INTO [" & tbl & "] ( Address1, Address2 ) SELECT [" & tbl2 & "]." & Me.Source_Address1,[" & tbl2 & "]." & Me.Source_Address2 FROM [" & tbl2 & "]"

Something is missing there, all line goes red.

As far as I understand this code will simply pull data from ImportTable and add it to Data table.
Straight from it, not via form.
Reason for the form is that I want user to pick which fields he wants to insert, so it needs to come from form dropdowns.

How can I refer to these form dropdowns to be ImportTable fields?

 

[theDBguy]

As far as I understand this code will simply pull data from ImportTable and add it to Data table.
Straight from it, not via form.
Reason for the form is that I want user to pick which fields he wants to insert, so it needs to come from form dropdowns.

How can I refer to these form dropdowns to be ImportTable fields?

Hi. We won't know for sure if your interpretation of that code is correct or not until you try it. If it doesn't work, we can give it another try. If it's red, there's a syntax error. Find it and fix it. I'm on my phone right now and can't check/verify the syntax at the moment. Sorry.

PS. I took a quick look and added a couple of &s in the above code. Pleas copy the code in my previous post again. Thanks.

 

[phinix]

Hi. We won't know for sure if your interpretation of that code is correct or not until you try it. If it doesn't work, we can give it another try. If it's red, there's a syntax error. Find it and fix it. I'm on my phone right now and can't check/verify the syntax at the moment. Sorry.

Sorry, I really appreciate your help.
Fixed it, gives me error: Compile error: Invalid use of Me keyword"

I've read some about "active" attribute.
How can we "point" to active form?
I believe "Me" was going to do that?

 

[theDBguy]

Sorry, I really appreciate your help.
Fixed it, gives me error: Compile error: Invalid use of Me keyword"

I've read some about "active" attribute.
How can we "point" to active form?
I believe "Me" was going to do that?

Hi. I think we're getting close. I assumed this code was in the form where they pick the fields. If not, we can try replacing Me with Forms!Form_MappingTables

 

[phinix]

Hi. I think we're getting close. I assumed this code was in the form where they pick the fields. If not, we can try replacing Me with Forms!Form_MappingTables

Holy S*** it worked!!!!
Thank you thank you !!!!

You always answer all my questions in minutes, you sure you're not some kind of forum bot?


OK, now I'm gonna try to point second part of the puzzle - that form has another dropdowns with destinations fields (Data table) - so this way I will be able to let user pick which fields go to which data element in Data table.

Maybe you remember my other question few days ago - I was trying to get user to have a form to "map" two tables and simply append records from one table to another by picking which fields go where.
This is another step to completing this

 

[theDBguy]

Holy S*** it worked!!!!
Thank you thank you !!!!

You always answer all my questions in minutes, you sure you're not some kind of forum bot?


OK, now I'm gonna try to point second part of the puzzle - that form has another dropdowns with destinations fields (Data table) - so this way I will be able to let user pick which fields go to which data element in Data table.

Maybe you remember my other question few days ago - I was trying to get user to have a form to "map" two tables and simply append records from one table to another by picking which fields go where.
This is another step to completing this

Hi. Glad to hear you got it to work. You simply apply the same principle we used for the FROM part into you INTO section. Debug.Print should guide you. Cheers!

 

[phinix]

Hi. Glad to hear you got it to work. You simply apply the same principle we used for the FROM part into you INTO section. Debug.Print should guide you. Cheers!

Yep, worked again! Now I can map other fields.

Thank you very much for your help

 

[theDBguy]

Yep, worked again! Now I can map other fields.

Thank you very much for your help

Hi. You're welcome. We're all happy to assist. Good luck with your project.

 

[phinix]

Hello!

I've continued working on this db and came up on another problem.

It all works fine when I "map" exact number of fields.
My query in code had 23 fields as this is total amount of fields in "Data" table (processing one where data is inserted to).
However "Source" table (imported one) won't always have full 23 fields to ideally match Data table.

This is the code I've run, whole function (form screenshot attached):

Public Function MappingForm() As Boolean
On Error Resume Next
  
Dim ws As Workspace
Dim db As DAO.Database
Dim strSQL As String
Dim table1 As String
Dim table2 As String
Dim frm As String
Dim Form_MappingTables As Form


Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
table1 = "Data"
table2 = "ImportTable"
frm = "Form_MappingTables"

On Error GoTo Proc_Err
'start a transaction to ensure all updates are run or rolled back
ws.BeginTrans
    
strSQL = "INSERT INTO [" & table1 & "] (" & Forms!Form_MappingTables.Data_1 & ", " & Forms!Form_MappingTables.Data_2 & ", " & Forms!Form_MappingTables.Data_3 & ", " & Forms!Form_MappingTables.Data_4 & _
", " & Forms!Form_MappingTables.Data_5 & ", " & Forms!Form_MappingTables.Data_6 & ", " & Forms!Form_MappingTables.Data_7 & ", " & Forms!Form_MappingTables.Data_8 & _
", " & Forms!Form_MappingTables.Data_9 & ", " & Forms!Form_MappingTables.Data_10 & ", " & Forms!Form_MappingTables.Data_11 & ", " & Forms!Form_MappingTables.Data_12 & _
", " & Forms!Form_MappingTables.Data_13 & ", " & Forms!Form_MappingTables.Data_14 & ", " & Forms!Form_MappingTables.Data_15 & ", " & Forms!Form_MappingTables.Data_16 & _
", " & Forms!Form_MappingTables.Data_17 & ", " & Forms!Form_MappingTables.Data_18 & ", " & Forms!Form_MappingTables.Data_19 & ", " & Forms!Form_MappingTables.Data_20 & _
", " & Forms!Form_MappingTables.Data_21 & ", " & Forms!Form_MappingTables.Data_22 & ", " & Forms!Form_MappingTables.Data_23 & _
") SELECT Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_1 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_2 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_3 & _
"]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_4 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_5 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_6 & _
"]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_7 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_8 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_9 & _
"]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_10 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_11 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_12 & _
"]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_13 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_14 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_15 & _
"]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_16 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_17 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_18 & _
"]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_19 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_20 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_21 & _
"]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_22 & "]), Nz([" & table2 & "].[" & Forms!Form_MappingTables.Source_23 & "]) FROM [" & table2 & "]"

Debug.Print strSQL


db.Execute strSQL, dbFailOnError


 'commit all changes
  ws.CommitTrans

Proc_Exit:
  Set ws = Nothing
  Set db = Nothing
  Exit Function

Proc_Err:
  ws.Rollback
  MsgBox "Error updating: " & Err.Description
  Resume Proc_Exit
End Function

Now, when debugging, I get this SQL string:

INSERT INTO [Data] (ContactID, Title, FirstName, Surname, Fullname, Flag1, Flag2, Address1, Address2, Address3, Town, County, Postcode, Flag3, Country, Flag4, Flag5, , , , , , ) SELECT Nz([ImportTable].[Person ID]), Nz([ImportTable].[Title]), Nz([ImportTable].[First Name]), Nz([ImportTable].[Surname]), Nz([ImportTable].[Fullname]), Nz([ImportTable].[Contact Point ID]), Nz([ImportTable].[Contact Detail]), Nz([ImportTable].[Add1]), Nz([ImportTable].[Add2]), Nz([ImportTable].[Add3]), Nz([ImportTable].[Town]), Nz([ImportTable].[County]), Nz([ImportTable].[PostCode]), Nz([ImportTable].[PostCode Area]), Nz([ImportTable].[Country]), Nz([ImportTable].[Job Title]), Nz([ImportTable].[Site Name]), Nz([ImportTable].[]), Nz([ImportTable].[]), Nz([ImportTable].[]), Nz([ImportTable].[]), Nz([ImportTable].[]), Nz([ImportTable].[]) FROM [ImportTable]

So you can see that not all fields were mapped as Source/Imported table doesn't have 23 fields, so its obvious - query will fail as there are some un-selected fields that are tried to be inserted to un-selected destination fields (in Data Table).

I need to figure out how am I going to solve this, to make it kinda dynamic, so when user won't match all fields, it will still run.

How can I achieve this?

EDIT:
I just tried to add IsNull condition to it, but this way I would need to add 23 conditions for all combinations of filled fields... There must be other way.

EDIT 2:
I just hit the wall - Procedure too long:banghead:
Too many conditions, need to make it sorter, or find out how to solve this other way.

 

[theDBguy]

Hi. I can think of at least two ways to get around this problem.


1. You can loop through the controls and construct your string based on whether the control is empty or not, or


2. Construct your string in sections depending on whether the control is empty or not.


To reduce the number of conditions, you could skip the minimum number of fields that will always be selected.

 

[phinix]

Hi. I can think of at least two ways to get around this problem.


1. You can loop through the controls and construct your string based on whether the control is empty or not, or


2. Construct your string in sections depending on whether the control is empty or not.


To reduce the number of conditions, you could skip the minimum number of fields that will always be selected.

Yes, What I've done is I decided that minimum of 5 fields must be mapped.
Then built conditions for rest of them.

See, I'm not a developer, but I would love to know how to do it your way No.1 or No.2

 

[theDBguy]

Yes, What I've done is I decided that minimum of 5 fields must be mapped.
Then built conditions for rest of them.

See, I'm not a developer, but I would love to know how to do it your way No.1 or No.2

For example, I might break the whole SQL into two strings:

strSQL and strSQL2

Then, with a minimum of five fields, I might start with:

strSQL = "SELECT INTO [" & table1 & "] (" & all the way to the first five fields only...
strSQL2 = ") SELECT 
 Nz([" & table2 & "].[" & all the way to the first five fields only...

After that, you can slowly add the rest of the fields one at a time. For example:

If Forms!FormName.Field6 & "" <>"" Then
    strSQL = strSQL & "," & add field six here...
    strSQL2 = strSQL2 & "," & add field six here...
End If

Then, at the end, you can combine both sections.


strSQL = strSQL & strSQL2


Hope it helps...