	AllExperts > Using MS Access
	Search

About Scottgem
Expertise
I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience
I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

You are here: Experts > Computing/Technology > Business Software > Using MS Access > Inserting records into temporary table based on selected existing records

Using MS Access - Inserting records into temporary table based on selected existing records

Expert: Scottgem - 6/29/2009

Question
Hi Scott, thanks for all your prior help. I am trying to insert records into a temporary table based on a selection from a combobox. The process is to produce a budget modification to the original budget and keeping the budget intact for historical purposes. Upon selection of a budget to modify, I want the program to pull records from the "tblBudgetLine" table into the form for editing. There most likely will be multiple records pulled into the form. The following is the code used.

Private Sub CboSelBudget_AfterUpdate()
'   Initialize variables
   Dim int_BdgtID As Integer
   Dim str1SQL As String
   Dim str2SQL As String
   Dim str3SQL As String
   Dim int_response As Integer
   Dim rst As Recordset
   Dim rs As Object
   Dim dbs As Database

   Set dbs = CurrentDb

   int_BdgtID = [Forms]![frmBudgetModSetup]![CboSelBudget]
   int_response = MsgBox("Are you sure you want to modify this budget?", 1, "Confirmation Required")

   str1SQL = "INSERT INTO tblTempBudMods (BdgtID, AccountNo, BModPriorApprAmt) " & _
   "SELECT tblBudgets.BdgtID, tblBudgets.AccountNo, tblBudgets.ApprovedBudget " & _
   "FROM tblBudgets WHERE tblBudgets.BdgtID = " & int_BdgtID & ";"

   str2SQL = "INSERT INTO tblTempBudgetModChanges ( TempBMLineID, BMLineAcctNo, BMPrevLineAmt, BMLineJustif ) " & _
   "SELECT tblBdgtLine.LineItemID, tblBdgtLine.LineAccountNo, tblBdgtLine.LineBudgetAmt, tblBdgtLine.LineJustif " & _
   "FROM tblBdgtLine;"

   str3SQL = "SELECT * FROM tblBdgtLine ORDER BY tblBdgtLine.BdgtID"

   If int_response = 1 Then

       CurrentDb.Execute str1SQL

       DoCmd.RunCommand acCmdRemoveFilterSort
       Set rs = Me.Recordset.Clone
       rs.FindFirst "[BdgtID] = " & int_BdgtID
       If Not rs.EOF Then Me.Bookmark = rs.Bookmark
       Me.Refresh

  ---> Set rst = dbs.OpenRecordset(str3SQL)
       With rst

           Do While Not .EOF

               If [BdgtID] = int_BdgtID Then
                   CurrentDb.Execute str2SQL
                   rst.MoveNext
               End If

           Loop
           .Close

       End With

       DoCmd.Requery "frmBudLineChangesDS"
       DoCmd.GoToControl "BModNo"

   Else

       CboSelAward = Null
       CboSelBudget = Null
       Me.Refresh
       DoCmd.GoToControl "cboSelAward"

   End If
End Sub

The error I receive is "type mismatch". I've "arrowed" to code that produces the error. Up to then, the code works, by bringing in the budget record from "tblBudgets", but not the line accounts linked to the budget record.

Your assistance is greatly appreciated.
BPinto

Answer
A data mismatch error means you are trying to use criteria that involves two different datatytpes. There are two places to look. First in the WHERE clause of str1SQL. If BdgtID is an autonumber then you need to use the LONG datatype not Integer. That's likely your problem.

The other place might be in str2SQL. You may not have used the data datatypes in tblTempBudgetModChanges as in tblBdgtLine.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Add to this Answer Ask a Question