Solved

Access Query Development

Posted on 2013-02-06
4
270 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Flowing down data to other tables 13 32
Menu Macro ‘Action Failed’, Access 2003 7 16
Access/Visual Basic Question 3 24
Microaoft Access on a SBS 2011 Server 4 15
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
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…

808 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