Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Query Development

Posted on 2013-02-06
4
Medium Priority
?
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1400 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 600 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

618 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