Update Query

How do I get one row of data from table A into a row in table B.

Normally I would use a combo but I have 40 fields and combo's only do 20.

I can specify the row in each table with a WHERE clause but what is the best way to do it. The new data may have to overwrite old.
Derek BrownMDAsked:
Who is Participating?
 
SheilsCommented:
I still not sure what you are trying to achieve but I will take a stab in the dark. If my response is not what you are after then try giving a short description of what you want the end result to be.

Try this on the combobox before update event

DimstrSQL as string, strvalue1 as string,strvalue2 as string,strvalue3 as string

strvalue1 =DLookup("FieldName1" , "TableName" , "fldID =" & Me. fldID )
strvalue2 =DLookup("FieldName2" , "TableName" , "fldID =" & Me. fldID )
strvalue3 =DLookup("FieldName3" , "TableName" , "fldID =" & Me. fldID )

strSQL="UPDATE TableA SET column1='" & strvalue1 & "', column2='" & strvalue2 & "',column3='" & strvalue3 & "WHERE AID=" Me.fldID

However, I note that I strongly suspect that the problem is with your table structure.The fact you need 40 columns suggests that there is something wrong with your setup. Thy posting an empty database with all the tables if you want some advise on the structure.

Remember getting the table structure right is the most important part of database development
0
 
IrogSintaCommented:
Your question is a bit vague.  I'm not sure what a combo box has to do with this.  Can you explain this a bit more?

Ron
0
 
SheilsCommented:
Are you trying to use table B as a lookup table for table A

OR

Are you trying to run an update query to update table A with some data from Table B
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Derek BrownMDAuthor Commented:
Sorry my questions are sometimes overcomplicated so I am trying to be more concise.

I am trying to create an update query to update table A with some data from Table B. If I were a normal programmer I would supply a link to the row in table B and display B directly in the form view by subform. But when you need 40 columns to describe an object let's say it is an item in an invoice if the client wants to change a couple of fields the correct way (I believe) is to Archive the changed record and create a new one ready for use in other Invoices. I'm not having my customers do this.

I have always felt that this is a bad use of normalisation. It may be more efficient use of the database but I believe applications should not designed for efficiency rather designed for ease of use. (Am I going on a bit here?)

So I want to select the data from table B and paste it directly into A. So I envisage a combo with the reference or description of the line of information in B and then code that gets the 40 fields data and sticks it in table A (Which would be the main forms table).

I do hope that this can be done. I am expecting:
Combo_after update
Find the 40 fields in a row in table B that matches the ID selected in the form's combo
Paste or update the row in A filtered by the current form's ID from table A

I am hoping that this will avoid me having to do a DLookUp for 40 fields individualy
0
 
Gustav BrockCIOCommented:
If the table schemas are identical, you can use a function like this which queries and copies a single record:
Public Function CopyRecord( _
  ByVal strTable As String, _
  ByVal strId As String, _
  ByVal lngId As Long) _
  As Boolean

  Dim dbs     As DAO.Database
  Dim rst     As DAO.Recordset
  Dim rstAdd  As DAO.Recordset
  Dim fld     As DAO.Field
  Dim strFld  As String
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * From " & strTable & " Where " & strId & "=" & lngId & ";")
  Set rstAdd = dbs.OpenRecordset("Select Top 1 * From " & strTable & ";")
'  Set rstAdd = rst.Clone
  
  With rstAdd
    .AddNew
      For Each fld In rstAdd.Fields
        With fld
          strFld = .Name
          If Not strFld = strId Then
            .Value = rst.Fields(strFld).Value
          End If
        End With
      Next
    .Update
    .Close
  End With
  rst.Close
  
  Set fld = Nothing
  Set rstAdd = Nothing
  Set rst = Nothing
  Set dbs = Nothing
  
End Function

Open in new window

This copies to the same table but you can easily adjust the code to use two different tables.

/gustav
0
 
Dale FyeCommented:
It sounds like all you really need is an Append Query, which you can build using the query builder.

Parameters [TableBID] long
INSERT INTO tableA (Field1, Field2, .... Field40)
SELECT Field1, Field2, ...Field40
FROM TableB
WHERE TableB.ID = [TableBID]

Save that query as something like qry_formname_CopyB_to_A

Then after selecting the record in B that you want, you would do something like this:
Private Sub cmd_Copy_Click

    Dim db as DAO.Database
    Dim qdf as DAO.Querydef

    set db = currentdb
    set qdf = db.querdefs("qry_formname_CopyB_to_A")
    qdf.parameters(0) = me.comboName
    qdf.Execute dbFailOnError

ProcExit:
    on error resume next
    set qdf = nothing
    set db = nothing
    Exit Sub

ProcError:
    msgbox err.number & vbcrlf & err.description, , "Error in record copy operation"
    debug.print "Error in record copy operation"
    debug.print err.number, err.description
    Resume ProcExit

End Sub

Open in new window

0
 
Derek BrownMDAuthor Commented:
Thank you Gustav.

Nice simple code but the tables are not identical in fact the main form has 223 columns. (That should set a few hearts racing)

Thanks sb9
I know that the way I do things is not normal but I have been doing this now for 20 years. My users appreciate that they change almost anything that they can see without going to maintenance changing tables copying stuff etc,  by just selecting from combo's (up to 20 columns)  It's like choosing stuff from a catalogue but my users want to change details of what they bought and in a conventional normalised database changing what they bought now changes what is in the catalogue. So before a change can be made a copy of the original has to be made and pasted back into the catalogue. As I said, I do not expect my customers to have to do that. Also when prices in the catalogue change a new set of data has to be produced to maintain integrity with all the past records using the original prices.

With my system customers can change there own records and what is in the catalogue without it effecting the original records (Quotations, Invoices, technical data etc.) Can you see the difference?

Fyed I am just trying your post.

Many thanks to you all.

Derek
0
 
Derek BrownMDAuthor Commented:
Hi All

Can I not put a WHERE clause here:

INSERT INTO tableA (Field1, Field2, .... Field40)

WHERE tableAID = Form!tableAID (Specify which row to insert into)

SELECT Field1, Field2, ...Field40
FROM TableB
WHERE TableB.ID = [TableBID]
0
 
Derek BrownMDAuthor Commented:
Hi Dale
When trying to save this query I get error "Syntax error in Parameters clause" any idea why? here is a short version of what I have:

Parameters [VPStyle] long

INSERT INTO VisionPanels ( VPStyle, DFT)
SELECT VPStylesFP.VPStyle, VPStylesFP.DFT,
FROM VPStylesFP
WHERE VPStylesFP.VPStyle = [VPStyle]

Can't see what's wrong VPStyle is an autonumber (Increment) in VPStyle and a Long in VisionPanels
0
 
Dale FyeCommented:
Oops,  The parameters line should be followed by a semi-colon.

Parameters [VPStyle] long;
INSERT INTO VisionPanels ( VPStyle, DFT)
SELECT VPStylesFP.VPStyle, VPStylesFP.DFT,
FROM VPStylesFP
WHERE VPStylesFP.VPStyle = [VPStyle]
0
 
Derek BrownMDAuthor Commented:
That works better but I now get error in the after update code on the combo

I called the query "Z"

Set qdf = db.querdefs("Z") where db.querdefsis highlighted in the code module

Which part of this code/ query selects the current selected row in the VisionPanel Table (formally Table A) to make sure it goes in the correct row
0
 
Derek BrownMDAuthor Commented:
Thanks you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.