Solved

Update Query

Posted on 2014-02-08
12
184 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
 
LVL 49

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now