use form to map one table to another

A

Abbey Normal

Hi. I have two GL tables that I need to map. One has "Our" key & desc - the
other has "their" key and description. I was hoping to do something where the
user selects a description from "our" table and then the description "their"
table. I would then populate the key and description from "theirs" into 2
extra fields into ours. Can someone get me started in the right direction?
I should be able work the details if I know whats the right approach. Thanks
for any suggestions...
 
S

Stefan Hoffmann

hi Abbey,

Abbey said:
Hi. I have two GL tables that I need to map. One has "Our" key & desc - the
other has "their" key and description. I was hoping to do something where the
user selects a description from "our" table and then the description "their"
table. I would then populate the key and description from "theirs" into 2
extra fields into ours. Can someone get me started in the right direction?
I should be able work the details if I know whats the right approach. Thanks
for any suggestions...
Click to expand...
Create a new query with a SQL statement like that:

SELECT O.*, T.*
FROM OurTable O
INNER JOIN TheirTable T
ON O.Key = T.Key

This will give you the matching records.

The UPDATE query should look like that:

UPDATE OurTable
INNER JOIN TheirTable
ON OurTable.Key = TheirTable.Key
SET OurTable.TheirKey = TheirTable.Key


mfG
--> stefan <--
 
A

Abbey Normal

oops, I should have said the description and keys in both tables are
different. the user is going to have to map the two by sight. I'm thinking
that I bring up "our" GL showing key/description and a drop down box to
bring up available "their" descriptions, once selected it would put that
description ("their") into addtl fields in "our" gl table and also put
"their" key into addtl fields in our table. would probably want to update
"their" table with a flag so that any selected GL will no longer appear in
the drop down list. I was thinking of using a combo box with two columns??
 
A

Abbey Normal

Actually, I think I've got myself started on the combo box thingie, but what
I'm getting hung up on now is setting the flag (Yes/No) in the "Their" file
to yes (?true) when they have clicked on it. do i need code for that or can i
just use an expression? thanks
 
S

Stefan Hoffmann

hi Abbey,

Abbey said:
oops, I should have said the description and keys in both tables are
different. the user is going to have to map the two by sight. I'm thinking
that I bring up "our" GL showing key/description and a drop down box to
bring up available "their" descriptions, once selected it would put that
description ("their") into addtl fields in "our" gl table and also put
"their" key into addtl fields in our table. would probably want to update
"their" table with a flag so that any selected GL will no longer appear in
the drop down list. I was thinking of using a combo box with two columns??
Click to expand...
I would use two subforms. Both in datasheet view. So the users can
scroll in both tables.
On then main form place a "match" button, which will connect the active
datarows of both subforms.

Private Sub btnMatch_Click

CurrentDb.Execute "UPDATE OurTable " & _
"SET TheirKey = " & frmRight![Key] & " " _
"WHERE OurKey = " & frmLeft![Key]


End Sub


mfG
--> stefan <--
 
A

Abbey Normal

Hi Stefan,
What an elegant solution! I'm just having a little trouble getting the code
to work. Getting a runtime 424 error. can you see what's wrong with this?
CurrentDb.Execute "UPDATE tblJDEGL SET tblJDEGL.TheirGl = " & frmRight![Key]
& "WHERE tblJDEGl =" & frmLeft![Key]
(i have it all on one line)

Stefan Hoffmann said:
hi Abbey,

Abbey said:
oops, I should have said the description and keys in both tables are
different. the user is going to have to map the two by sight. I'm thinking
that I bring up "our" GL showing key/description and a drop down box to
bring up available "their" descriptions, once selected it would put that
description ("their") into addtl fields in "our" gl table and also put
"their" key into addtl fields in our table. would probably want to update
"their" table with a flag so that any selected GL will no longer appear in
the drop down list. I was thinking of using a combo box with two columns??
Click to expand...
I would use two subforms. Both in datasheet view. So the users can
scroll in both tables.
On then main form place a "match" button, which will connect the active
datarows of both subforms.

Private Sub btnMatch_Click

CurrentDb.Execute "UPDATE OurTable " & _
"SET TheirKey = " & frmRight![Key] & " " _
"WHERE OurKey = " & frmLeft![Key]


End Sub


mfG
--> stefan <--
Click to expand...
 
A

Abbey Normal

okay, now i've change the code to this and I'm getting a syntax error(its
stopping at WHERE)

CurrentDb.Execute "UPDATE tblJDEGL " & _
"SET tblJDEGL.theirGL = " & tblSafGl_subform1![safGL] & " " _
"WHERE tblJDEGL.jdeGl = " & tblJDEGL_subform![jdeGl]


Abbey Normal said:
Hi Stefan,
What an elegant solution! I'm just having a little trouble getting the code
to work. Getting a runtime 424 error. can you see what's wrong with this?
CurrentDb.Execute "UPDATE tblJDEGL SET tblJDEGL.TheirGl = " & frmRight![Key]
& "WHERE tblJDEGl =" & frmLeft![Key]
(i have it all on one line)

Stefan Hoffmann said:
hi Abbey,

Abbey said:
oops, I should have said the description and keys in both tables are
different. the user is going to have to map the two by sight. I'm thinking
that I bring up "our" GL showing key/description and a drop down box to
bring up available "their" descriptions, once selected it would put that
description ("their") into addtl fields in "our" gl table and also put
"their" key into addtl fields in our table. would probably want to update
"their" table with a flag so that any selected GL will no longer appear in
the drop down list. I was thinking of using a combo box with two columns??
Click to expand...
I would use two subforms. Both in datasheet view. So the users can
scroll in both tables.
On then main form place a "match" button, which will connect the active
datarows of both subforms.

Private Sub btnMatch_Click

CurrentDb.Execute "UPDATE OurTable " & _
"SET TheirKey = " & frmRight![Key] & " " _
"WHERE OurKey = " & frmLeft![Key]


End Sub


mfG
--> stefan <--
Click to expand...
Click to expand...
 
A

Abbey Normal

Phew! finally got the syntax right! thanks for a great solution, Stefan!
Dim strSql As String
strSql = "UPDATE tblJDEGL SET theirGL = """ & subSafGL![safgl] & """
WHERE jdeGL = """ & subjdeGL![jdeGl] & """;"
CurrentDb.Execute strSql, dbFailOnError
subjdeGL!theirGL.Requery

End Sub

Abbey Normal said:
okay, now i've change the code to this and I'm getting a syntax error(its
stopping at WHERE)

CurrentDb.Execute "UPDATE tblJDEGL " & _
"SET tblJDEGL.theirGL = " & tblSafGl_subform1![safGL] & " " _
"WHERE tblJDEGL.jdeGl = " & tblJDEGL_subform![jdeGl]


Abbey Normal said:
Hi Stefan,
What an elegant solution! I'm just having a little trouble getting the code
to work. Getting a runtime 424 error. can you see what's wrong with this?
CurrentDb.Execute "UPDATE tblJDEGL SET tblJDEGL.TheirGl = " & frmRight![Key]
& "WHERE tblJDEGl =" & frmLeft![Key]
(i have it all on one line)

Stefan Hoffmann said:
hi Abbey,

Abbey Normal wrote:
oops, I should have said the description and keys in both tables are
different. the user is going to have to map the two by sight. I'm thinking
that I bring up "our" GL showing key/description and a drop down box to
bring up available "their" descriptions, once selected it would put that
description ("their") into addtl fields in "our" gl table and also put
"their" key into addtl fields in our table. would probably want to update
"their" table with a flag so that any selected GL will no longer appear in
the drop down list. I was thinking of using a combo box with two columns??
I would use two subforms. Both in datasheet view. So the users can
scroll in both tables.
On then main form place a "match" button, which will connect the active
datarows of both subforms.

Private Sub btnMatch_Click

CurrentDb.Execute "UPDATE OurTable " & _
"SET TheirKey = " & frmRight![Key] & " " _
"WHERE OurKey = " & frmLeft![Key]


End Sub


mfG
--> stefan <--
Click to expand...
Click to expand...
Click to expand...
 
S

Stefan Hoffmann

hi Abbey,

Abbey said:
Phew! finally got the syntax right! thanks for a great solution, Stefan!
Dim strSql As String
strSql = "UPDATE tblJDEGL SET theirGL = """ & subSafGL![safgl] & """
WHERE jdeGL = """ & subjdeGL![jdeGl] & """;"
CurrentDb.Execute strSql, dbFailOnError
subjdeGL!theirGL.Requery

End Sub
Click to expand...
You can use a single ' instead of a double ", to make it easier to read:

strSql = "UPDATE tblJDEGL " & _
"SET theirGL = '" & subSafGL![safgl] & "' " & _
"WHERE jdeGL = '" & subjdeGL![jdeGl] & "';"



In both solutions you have to escape the string delimiter, to aviod SQL
injection and to allow to input of ' or " as a content character:

strSql = "UPDATE tblJDEGL " & _
"SET theirGL = """ & SQLEscape(subSafGL![safgl], """") & _
""" " & _
"WHERE jdeGL = """ & SQLEscape(subjdeGL![jdeGl], """") & _
& """;"

or

strSql = "UPDATE tblJDEGL " & _
"SET theirGL = '" & SQLEscape(subSafGL![safgl]) & "' " & _
"WHERE jdeGL = '" & SQLEscape(subjdeGL![jdeGl]) & "';"

with

Public Function SQLEscape(AString As String, _
Optional ADelimiter As String = "'" _
) As String

' VBA.Strings.Replace requires A2K or higher.
SQLEscape = Replace(AString, ADelimiter, ADelimiter & ADelimiter)

End Function


mfG
--> stefan <--
 
Top