Page 3 of 7 FirstFirst 1234567 LastLast
Results 31 to 45 of 104
  1. #31
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

    Quote Originally Posted by tbbrown32 View Post
    It's closer to working, but I'm getting the 'Too few parameters. Expected 1." error. It's highlighting this line of code:
    Code:
    CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');"
    This usually means you have not spelled something correctly. You do know that a SubForm can have a different RecordSource so you could eliminate the SQL, right?

  2. #32
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    I know that a subform can have a different recordsource, but I was under the impression from this thread that I can't do all 3 things listed below without SQL.

    1. Cascade the cbos
    2. Write the value selected in each cbo into the appropriate table
    3. Have the cbos update based on what Incident I am looking at

    If it's possible to do it without SQL, that'd be great, but thought that doing all of this required it.

  3. #33
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Incidentally, this code compiles after I updated the table name but doesn't insert.

    Code:
    Private Sub Combo573_AfterUpdate()
       Dim x As Integer
       x = [Forms]![frmNewMain]![Combo573]
       If x = True Then
          CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');"
       Else
          CurrentDb.Execute "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND  AgencyID = " & x, dbFailOnError
       End If
    End Sub

  4. #34
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I suspect you may have a design issue but can't be sure with the sample you supplied so far. It bothers me that you are needing to deal with so many tables at the same time. RDBMS are supposed to take care of that for you.

  5. #35
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Agreed, but the design is more complex (specifically referring to the tables the cbos are involved in) because an incident can be an agency, a department, or a program or all three, so I have to relate them back to the Incident table as well as relate them to each other in the hierarchy. Admitted, I find that to be more of a pain from the design standpoint, but there's not much else that can be done that I'm aware of.

  6. #36
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Cobbling together kluges to compensate for a questionable design will only get worse. Better to address the design issue early in the process. You might find later on that some issues are just to complex to code around. Just my $0.02.

  7. #37
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    I see what you're saying (and agree), but unfortunately the way they collect data and the way the data is interrelated makes the current design a necessity. Incidentally I was able to get the code below to compile, but it doesn't insert into the table. Didn't know if you had any thoughts along that line. Thanks!
    Code:
    Private Sub Combo573_AfterUpdate()   Dim x As Integer
       x = [Forms]![frmNewMain]![Combo573]
       If x = True Then
          CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');"
       Else
          CurrentDb.Execute "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND  AgencyID = " & x, dbFailOnError
       End If End Sub

  8. #38
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about:
    Code:
    Private Sub Combo573_AfterUpdate()
       Dim x As Integer
       x = [Forms]![frmNewMain]![Combo573]
       If x = True Then
          CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');", dbFailOnError
       Else
          CurrentDb.Execute "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND  AgencyID = 'x'", dbFailOnError
       End If
    End Sub
    so you can see what is happening.

  9. #39
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    I added the dbFailOnError, but nothing shows up since it doesn't fail, it just doesn't write to the table. Odd, no error but no Insert either.

  10. #40
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How about adding error handling to that procedure? I'm sure it is failing. If you need help just ask.

  11. #41
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's some error handling:
    Code:
    Private Sub Combo573_AfterUpdate()
       On Error GoTo Combo573Error
    
       Dim x As Integer
       x = [Forms]![frmNewMain]![Combo573]
       If x = True Then
          CurrentDb.Execute "INSERT INTO tblAgencyIncident(InternalIncidentID, AgencyID) Values (" & Me.InternalIncidentID & ", 'x');", dbFailOnError
       Else
          CurrentDb.Execute "DELETE FROM tblAgencyIncident WHERE InternalIncidentID = '" & Me.InternalIncidentID & "' AND  AgencyID = 'x'", dbFailOnError
       End If
    ExitCombo573:
       Exit Sub
    
    Combo573Error:
       MsgBox Err & ": " & vbCrLf & Err.Description
       Resume ExitCombo573
    
    End Sub

  12. #42
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Thanks for the error handling code! For some reason, no errors show still, it just doesn't insert. Not really sure where to go from here

  13. #43
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you single stepped the code to see what is happening?

  14. #44
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If no error is generated then it is simply not meeting the selection criteria.

  15. #45
    nick404's Avatar
    nick404 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2015
    Location
    Wisconsin
    Posts
    352
    Might not be worth much help but - have you tried printing to immediate window the values that you are trying to insert? Maybe the process isn't collecting the correct values and might not have anything to insert. Just another thought.

Page 3 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 09-03-2015, 11:12 AM
  2. Replies: 4
    Last Post: 06-18-2014, 08:31 PM
  3. Combo box to filter a combo box
    By svrich in forum Access
    Replies: 20
    Last Post: 04-13-2014, 10:36 PM
  4. Replies: 1
    Last Post: 10-01-2013, 09:25 PM
  5. Combo Box filter – help!
    By catat in forum Forms
    Replies: 1
    Last Post: 08-24-2010, 04:15 PM

Tags for this Thread

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