Solved

Update Query

Posted on 2014-02-08
12
183 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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

20 Experts available now in Live!

Get 1:1 Help Now