Solved

Update Query

Posted on 2014-02-08
12
187 Views
Last Modified: 2014-02-14
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.
0
Comment
Question by:DatabaseDek
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39844508
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
 
LVL 16

Expert Comment

by:Sheils
ID: 39844555
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
 

Author Comment

by:DatabaseDek
ID: 39845074
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39845085
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
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 39845090
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39845187
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
 

Author Comment

by:DatabaseDek
ID: 39845252
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
 

Author Comment

by:DatabaseDek
ID: 39847601
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
 

Author Comment

by:DatabaseDek
ID: 39848021
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39848042
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
 

Author Comment

by:DatabaseDek
ID: 39849772
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
 

Author Closing Comment

by:DatabaseDek
ID: 39858581
Thanks you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question