Results 1 to 9 of 9
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Query Values dont match destination Issue on form load

    WorkArea.zip

    Hi all,
    I attached a db and having an Debug Issue that i have not been able to solve. Giving me an error
    about query values dont match the destination....
    I think i know why but not sure how to fix it.
    Its on onLoad of a form.


    db opens to company, the form in question is sfrmWorkArea.
    I dont beleive its a hard thing to fix, i just dont know how to fix it.
    Please assist and thank you,
    Dave

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you are talking about this line

    Code:
    DoCmd.RunSQL "Insert Into tblStateSelect (StateID, State, StateName) Select C.StateID, R.State From tblCompanyWorkArea C Inner Join tblState R ON C.StateID = R.StateID Where CompanyID = " & Nz(Me.cboCompanyID, 0)

    the error message is quite clear




    you want to insert 3 fields, but only select 2

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi all,
    Sorry for delay, had an emergency and have been gone all day.
    You hit that on the head for sure Ajax as i though that was the issue but I dont know how to fix it?
    I had this code wrote for a different form long ago and borrowed some of it for this form so not sure
    where he came up with the C. and R.
    I tried many different senarios to add that 3rd field but with no success, just created a different error.
    ...So, any ideas how to fix this please
    Thanks

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would have thought that you would get the statename from tblstate?

    However I would also think you would only need the tblstateID as you can get the rest of the tblstate data with that field?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    The form works as long as I dont add the StateName to it except for it doesnt put the "StateName" in the Selected WorkArea list box. It just puts in the "State"
    If I add the StateName to this, then it gives me this error.
    Can anyone tell me how to fix this please. The destination is to have a list box with available states and StateNames in it, then put selected ones into SelecteWorkArea listbox,
    and save to Table and then on load put those for the CompanyID that were previously selected back in that Selected WorkArea list box. If i add to it, then save, if delete, then remove
    and save.

    The on load code seems to be the issue and I do not know how to fix that code to add "StateName" to it as i am lost after the C. and R. stuff?
    I put the code in RED that is triggering the Debug Event....
    Thanks
    See Code
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cboCompanyID_AfterUpdate()
    
    
        If Nz(Me.cboCompanyID, 0) > 0 Then
    
    
            DoEvents
            DoCmd.SetWarnings False
            DoCmd.RunSQL "Insert Into tblStateSelect (StateID, State, StateName) Select C.StateID, R.State  From tblCompanyWorkArea C Inner Join tblState R ON C.StateID = R.StateID Where CompanyID = " & Nz(Me.cboCompanyID, 0)
            DoCmd.SetWarnings True
            DoEvents
            Me.cboSelectWorkArea.Requery
    
    
        End If
    End Sub
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    Private Sub cmdAddWorkArea_Click()
    
    
    On Error Resume Next
    Dim ctl As Control
    Dim Itm As Variant
    
    
        DoCmd.SetWarnings False
        Set ctl = Me.cboAvailableWorkArea
        For Each Itm In ctl.ItemsSelected
            DoCmd.RunSQL "Insert Into tblStateSelect (StateID, State, StateName) Select StateID, State, StateName From tblState Where StateID = " & ctl.ItemData(Itm)
        Next Itm
            DoCmd.SetWarnings True
            Me.cboSelectWorkArea.Requery
        
    End Sub
    
    
    Private Sub cmdAddWorkAreaAll_Click()
    
    
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "WorkAreaAppQry"
        DoCmd.SetWarnings True
        Me.cboSelectWorkArea.Requery
        
        
    End Sub
    
    
    
    
    Private Sub cmdRemoveWorkArea_Click()
    
    
    On Error Resume Next
    Dim ctl As Control
    Dim Itm As Variant
    
    
        DoCmd.SetWarnings False
        Set ctl = Me.cboSelectWorkArea
        For Each Itm In ctl.ItemsSelected
            DoCmd.RunSQL "Delete From tblStateSelect Where StateID = " & ctl.ItemData(Itm)
        Next Itm
            DoCmd.SetWarnings True
            Me.cboSelectWorkArea.Requery
            
    End Sub
    
    
    Public Sub CmdRemoveWorkAreaAll_Click()
        DoCmd.SetWarnings False
        DoCmd.RunSQL "Delete * From tblStateSelect"
        DoCmd.SetWarnings True
        Me.cboSelectWorkArea.Requery
    End Sub
    
    
    
    
    Private Sub cmdSave_Click()
    
    
        
        CurrentDb.Execute "Delete * From tblCompanyWorkArea Where CompanyID = " & Nz(Me.cboCompanyID, 0)
        CurrentDb.Execute "Insert INTO tblCompanyWorkArea (CompanyID, StateID, State, StateName) SELECT " & Nz(Me.cboCompanyID, 0) & ",StateID , State , StateName From tblStateSelect"
        DoEvents
    
    
    End Sub
    
    
    Private Sub Form_Current()
    Me.Form.Refresh
    End Sub
    
    
    Private Sub Form_Load()
        doClear
            If Nz(Me.cboCompanyID, 0) > 0 Then
    
    
            DoEvents
            DoCmd.SetWarnings False
            DoCmd.RunSQL "Insert Into tblStateSelect (StateID, State, StateName) Select C.StateID, R.State From tblCompanyWorkArea C Inner Join tblState R ON C.StateID = R.StateID  Where CompanyID = " & Nz(Me.cboCompanyID, 0)
            DoCmd.SetWarnings True
            DoEvents
            Me.cboSelectWorkArea.Requery
            Me.cboAvailableWorkArea.Requery
    
    
        End If
    End Sub
    
    
    Public Sub doClear()
        Me.CmdRemoveWorkAreaAll_Click
    
    
    End Sub

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Well as you are getting State from table alias R, don't you think that is where you would be getting your statename from also?

    Whilst it does not apply in this particular case, what if this was inserting customer names like Daphne Peirce, and then she got married and became Daphne Smith?
    How would you update all those tables to reflect the new name, when if you referred to the customer table with it's custID, you would pick up the latest data.?

    I cannot test the logic, as there is nothing in tableCompanyWorkArea ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    C and R are just aliases for tables, cuts down on typing and can make the code easier to read

    Would think just add r.state name to the select part of the query

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Ajax View Post
    C and R are just aliases for tables, cuts down on typing and can make the code easier to read

    Would think just add r.state name to the select part of the query
    I am aware of the alias, I even mentioned it?

    I am trying to point the o/p to the problem, or what I believe is the problem, rather than hand the o/p a fish on a plate.
    As I mentioned, there is no data in the tblCompanyWorkArea, so the query as it stands produces nothing, whether you add statename or not.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you,
    It worked.
    That was just great!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-06-2020, 11:50 AM
  2. Import Data from Excel when columns dont match
    By NickWren in forum Import/Export Data
    Replies: 3
    Last Post: 01-29-2016, 09:14 AM
  3. Replies: 6
    Last Post: 10-29-2013, 12:59 PM
  4. Where two things DONT match
    By stephenaa5 in forum Queries
    Replies: 3
    Last Post: 11-09-2012, 03:51 PM
  5. Replies: 10
    Last Post: 12-15-2010, 11:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums