use form to map one table to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Abbey Normal - 13 Oct 2006 04:11 GMT

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...

Reply to this Message

Stefan Hoffmann - 13 Oct 2006 09:13 GMT

hi Abbey,

> 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
[quoted text clipped - 3 lines]
> I should be able work the details if I know whats the right approach. Thanks
> for any suggestions...

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 <--

Reply to this Message

Abbey Normal - 13 Oct 2006 12:52 GMT

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??

> hi Abbey,
>
[quoted text clipped - 23 lines]
> mfG
> --> stefan <--

Reply to this Message

Abbey Normal - 13 Oct 2006 14:02 GMT

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

> 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
[quoted text clipped - 32 lines]
> > mfG
> > --> stefan <--

Reply to this Message

Stefan Hoffmann - 13 Oct 2006 14:15 GMT

hi Abbey,

> 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
[quoted text clipped - 4 lines]
> "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 <--

Reply to this Message

Abbey Normal - 14 Oct 2006 06:22 GMT

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)

> hi Abbey,
>
[quoted text clipped - 21 lines]
> mfG
> --> stefan <--

Reply to this Message

Abbey Normal - 14 Oct 2006 17:03 GMT

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]

> Hi Stefan,
> What an elegant solution! I'm just having a little trouble getting the code
[quoted text clipped - 28 lines]
> > mfG
> > --> stefan <--

Reply to this Message

Abbey Normal - 15 Oct 2006 13:46 GMT

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

> okay, now i've change the code to this and I'm getting a syntax error(its
> stopping at WHERE)
[quoted text clipped - 35 lines]
> > > mfG
> > > --> stefan <--

Reply to this Message

Stefan Hoffmann - 16 Oct 2006 09:27 GMT

hi Abbey,

> Phew! finally got the syntax right! thanks for a great solution, Stefan!
> Dim strSql As String
[quoted text clipped - 4 lines]
>
> End Sub

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 <--

Reply to this Message