Solved

Access Query Development

Posted on 2013-02-06
4
268 Views
Last Modified: 2013-02-06
Hi,

How do I create an MS Access query which will populate multiple rows in a table based on selecting row from an existing table 'where' the selection criteria is based on the value from an open form.

the SQL pseudo code would be something like:

select * into NewTable
from OldTable
where field value in OldTable = archive_id from a form

Thanks
AEN
0
Comment
Question by:allfredeneuman
  • 2
4 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38859103
select * into NewTable
from OldTable
where OldTable.YourField = Forms!YourFormName!archive_id 

Open in new window

0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 350 total points
ID: 38859129
Just note that if both tables contain a PK or otherwise unique field, you may need to spell out all of the fields in order to insert everything other than the PK field to avoid errors caused by duplicating values in fields that only allow unique values, or errors caused by trying to insert a value into an autonumber field.


select Field1, Field2, Field3 etc into NewTable
from OldTable
where OldTable.YourField = Forms!YourFormName!archive_id 

Open in new window

0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 150 total points
ID: 38860140
Here is some code I use to copy data from one form record to another.  It uses a value in the Tag property of controls to determine which controls to copy (typically, some should not be copied).  The simple version is rarely useful, since there is almost always an AutoNumber key field that can't be copied.

Simple version -- OK if there is no AutoNumber or other key field in the record source

   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.RunCommand acCmdPasteAppend

This one works with any record source -- put "No Copy" in the Tag property of the controls whose values should not be copied.

It is called from a command button on a form, like this:

   Call CopyRecord(Me)

Public Sub CopyRecord(frm As Access.Form)
'Created by Helen Feddema 26-Sep-2009
'Last modified 26-Sep-2009

On Error GoTo ErrorHandler

   Dim CopyFields() As Variant
   
   lngCount = CountControls(frm)
   Debug.Print "Number of controls to copy: " & lngCount
   
   'Redimension array with actual number of controls to copy
   ReDim CopyFields(lngCount - 1, 1)
   
   'Cycle through all controls on form Detail section (except those
   'marked "No Copy") and save to an array
   lngCount = 0
   lngArrayNo = 0
   
   For Each ctl In frm.Section(acDetail).Controls
      strControlName = ctl.Name
      Debug.Print "Control name: " & strControlName _
         & "; tag: " & ctl.Tag
         
      If InStr(ctl.Tag, "No Copy") = 0 Then
      
         'Check field type and skip those that don't have values
         Debug.Print "Control name: " & strControlName
         lngControlType = ctl.ControlType
         
         Select Case lngControlType
            'Controls with values
            Case acTextBox
               Debug.Print "Control source: " & ctl.ControlSource
               GoTo Copy
            
            Case acComboBox
               GoTo Copy
            
            Case acCheckBox
               GoTo Copy
            
            Case acBoundObjectFrame
               GoTo Copy
               
            Case acListBox
               GoTo Copy
           
            Case acOptionGroup
               GoTo Copy
            
            Case acOptionButton
               'Check for control source, and only copy from
               'option buttons with one (bound option buttons)
               strTest = ctl.ControlSource
               GoTo Copy
               
            Case Else
               'No Value property; nothing to copy
               GoTo NoCopy
         End Select
Copy:
         strFieldName = ctl.ControlSource
         varFieldValue = ctl.Value
         Debug.Print strFieldName & " value: " & varFieldValue
         CopyFields(lngArrayNo, 0) = strFieldName
         CopyFields(lngArrayNo, 1) = varFieldValue
         lngArrayNo = lngArrayNo + 1
         lngCount = lngCount + 1
NoCopy:
      End If
   Next ctl

   Debug.Print "Number of controls to copy: " & lngCount
   
   'Create recordset, add a new record, and copy data to it from array
   Set dbs = CurrentDb
   strTable = "Pharmacy Scheduling"
   
   Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset)
   With rst
      .AddNew
      For lngArrayCount = 0 To lngCount - 1
         Debug.Print "Processing row " & lngArrayCount
         strFieldName = CopyFields(lngArrayCount, 0)
         varFieldValue = CopyFields(lngArrayCount, 1)
         Debug.Print "Field name: " & strFieldName
         Debug.Print "Field value: " & varFieldValue
         If strFieldName <> "" Then
            .Fields(strFieldName) = varFieldValue
         End If
      Next lngArrayCount
      .Update
      .Close
   End With
      
   'Requery form and go to new record
   frm.Requery
   DoCmd.GoToRecord objecttype:=acDataForm, _
      objectname:=frm.Name, _
      record:=acLast
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   If Err.Number = 2455 Then
      'Unbound option button
      Resume NoCopy
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & _
         Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function CountControls(frm As Access.Form) As Long
'Created by Helen Feddema 26-Sep-2009
'Last modified 26-Sep-2009

On Error GoTo ErrorHandler
   
   'Cycle through all controls on form Detail section (except those
   'marked "No Copy") and determine number of controls to copy
   lngCount = 0
   strTest = ""
   
   For Each ctl In frm.Section(acDetail).Controls
      strControlName = ctl.Name
      'Debug.Print "Control name: " & strControlName
      
      If InStr(ctl.Tag, "No Copy") = 0 Then
      
         'Check field type and skip those that don't have values
         lngControlType = ctl.ControlType
         
         Select Case lngControlType
            'Controls with values
            Case acTextBox
               GoTo CountCtl
            
            Case acComboBox
               GoTo CountCtl
            
            Case acCheckBox
               GoTo CountCtl
            
            Case acBoundObjectFrame
               GoTo CountCtl
               
            Case acListBox
               GoTo CountCtl
           
            Case acOptionGroup
               GoTo CountCtl
            
            Case acOptionButton
               'Check for control source, and only count
               'option buttons with one (bound option buttons)
               strTest = ctl.ControlSource
               If strTest <> "" Then
                  GoTo CountCtl
               End If
               
            Case Else
               'No Value property; don't count
               GoTo NoCount
         End Select
         
CountCtl:
         lngCount = lngCount + 1
NoCount:
      End If
   
      strTest = ""
   Next ctl

   Debug.Print "Number of controls to copy: " & lngCount
   CountControls = lngCount
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 2455 Then
      'Unbound option button
      Resume NoCount
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & _
         Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Open in new window

0
 

Author Closing Comment

by:allfredeneuman
ID: 38861797
I accepted multiple solutions. mbizup got most of the points but I could see myself using Helen_Feddema solution in another scenario.

Thank 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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

864 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

22 Experts available now in Live!

Get 1:1 Help Now