none
HOW TO DEFINE EOF AND BOF USING THE ID OF CERTAIN RECORDS IN A TABLE? AND ANOTHER QUESTION RRS feed

  • Question

  • Hello, I'm stuck, again... heh

    Here's the problem... the database I'm building requires multiple departments to edit data specific to their department. There is a limit to the number of records per department (each department only has 67 records they can edit). Instead of creating over a dozen tables and hundreds of forms and queries to match those tables, I know I can create all my limited data entries in 1 table and link a single form and query to filter only the specified records certain users can have when they login to the database. My problem is occurring with my main form that has 67 subforms that all contain a different record for viewing simultaneously. This form has a loop running, but without defining what record is the BOF and what record is the EOF the form will loop through the entire table instead of just the records I selected. I will post my loop code so you see what I'm talking about.

    My other sort of misc. question attached to this post: Is there a way to increase the amount of subforms I can add and loop without receiving an "out of memory" error from access?

    Private Sub Form_Load()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C")
    
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
          
    
    Me!sub01.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=1"
        rs.MoveNext
    
    Me!Sub02.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=2"
        rs.MoveNext
    
    Me!sub03.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=3"
        rs.MoveNext
    
    Me!sub04.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=4"
        rs.MoveNext
    
    Me!sub05.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=5"
        rs.MoveNext
    
    Me!sub06.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=6"
        rs.MoveNext
    
    Me!sub07.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=7"
        rs.MoveNext
    
    Me!sub08.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=8"
        rs.MoveNext
    
    Me!sub09.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=9"
        rs.MoveNext
    
    Me!sub10.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=10"
        rs.MoveNext
    
    Me!sub11.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=11"
        rs.MoveNext
    
    Me!sub12.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=12"
        rs.MoveNext
    
    Me!sub13.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=13"
        rs.MoveNext
    
    Me!sub14.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=14"
        rs.MoveNext
    
    Me!sub15.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=15"
        rs.MoveNext
    
    Me!sub16.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=16"
        rs.MoveNext
    
    Me!sub17.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=17"
        rs.MoveNext
    
    Me!sub18.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=18"
        rs.MoveNext
    
    Me!sub19.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=19"
        rs.MoveNext
    
    Me!sub20.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=20"
        rs.MoveNext
    
    Me!sub21.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=21"
        rs.MoveNext
    
    Me!sub22.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=22"
        rs.MoveNext
    
    Me!sub23.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=23"
        rs.MoveNext
    
    Me!sub24.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=24"
        rs.MoveNext
    
    Me!sub25.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=25"
        rs.MoveNext
    
    Me!sub26.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=26"
        rs.MoveNext
    
    Me!sub27.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=27"
        rs.MoveNext
    
    Me!sub28.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=28"
        rs.MoveNext
    
    Me!sub29.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=29"
        rs.MoveNext
    
    Me!sub30.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=30"
        rs.MoveNext
    
    Me!sub31.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=31"
        rs.MoveNext
    
    Me!sub32.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=32"
        rs.MoveNext
    
    Me!sub33.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=33"
        rs.MoveNext
    
    Me!sub34.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=34"
        rs.MoveNext
    
    Me!sub35.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=35"
        rs.MoveNext
    
    Me!sub36.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=36"
        rs.MoveNext
    
    Me!sub37.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=37"
        rs.MoveNext
    
    Me!sub38.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=38"
        rs.MoveNext
    
    Me!sub39.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=39"
        rs.MoveNext
    
    Me!sub40.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=40"
        rs.MoveNext
    
    Me!sub41.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=41"
        rs.MoveNext
    
    Me!sub42.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=42"
        rs.MoveNext
    
    Me!sub43.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=43"
        rs.MoveNext
    
    Me!sub44.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=44"
        rs.MoveNext
    
    Me!sub45.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=45"
        rs.MoveNext
    
    Me!sub46.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=46"
        rs.MoveNext
    
    Me!sub47.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=47"
        rs.MoveNext
    
    Me!sub48.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=48"
        rs.MoveNext
    
    Me!sub49.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=49"
        rs.MoveNext
    
    Me!sub50.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=50"
        rs.MoveNext
    
    Me!sub51.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=51"
        rs.MoveNext
    
    Me!sub52.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=52"
        rs.MoveNext
    
    Me!sub53.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=53"
        rs.MoveNext
    
    Me!sub54.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=54"
        rs.MoveNext
    
    Me!sub55.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=55"
        rs.MoveNext
    
    Me!sub56.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=56"
        rs.MoveNext
    
    Me!sub57.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=57"
        rs.MoveNext
    
    Me!sub58.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=58"
        rs.MoveNext
    
    Me!sub59.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=59"
        rs.MoveNext
    
    Me!sub60.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=60"
        rs.MoveNext
    
    Me!sub61.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=61"
        rs.MoveNext
    
    Me!sub62.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=62"
        rs.MoveNext
    
    Me!sub63.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=63"
        rs.MoveNext
    
    Me!sub64.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=64"
        rs.MoveNext
    
    Me!sub65.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=65"
        rs.MoveNext
    
    Me!sub66.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=66"
        rs.MoveNext
    
    Me!sub67.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=67"
        rs.MoveNext
    
        Loop
    Else
    
    End If
    rs.Close
    Set rs = Nothing
    
    End Sub


    • Edited by InnVis Friday, August 11, 2017 3:35 PM
    Friday, August 11, 2017 3:33 PM

Answers

  •     If rs!UserAccessType = 18 Then
            DoCmd.OpenForm "frmStartShell_ADMIN"
        ElseIf rs!UserAccessType = 1 Then
            DoCmd.OpenForm "frmMain_3C"
        ElseIf rs!UserAccessType = 3 Then
            DoCmd.OpenForm "frmStartShell"
        ElseIf rs!UserAccessType = 1 Then
            DoCmd.OpenForm "frmStartShell_STUDENT"
        End If
    
    Instead of the last If "DoCmd.OpenFom" line can the filter be added here

    Hi Jamie,

    rs!UserAccessType = 1  has two occurances in the If-then-else statement. The second one thus will never occur.

    OpenArgs is the 7th argument in the OpenForm method.

    Try to open the form (frmMain_3C?) using:    DoCmd.OpenForm "frmMain_3C",,,,,,"3-East"

    In  the Load event you change the sql-string in:

        Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE Service = '" & Me.OpenArgs & "'")

    See now what happens with the opened form. And what happens if you use:

         DoCmd.OpenForm "frmMain_3C",,,,,,"4-Center"

    Imb.

     

    • Marked as answer by InnVis Sunday, August 13, 2017 12:38 PM
    Saturday, August 12, 2017 7:07 PM
  •       
          Select Case rs!Branch
          Case "": .Form.Detail.BackColor = 9342606
          Case "USN": .Form.Detail.BackColor = 8388608
          Case "USA": .Form.Detail.BackColor = 1007160
          End Select
    

    Hi Jamie,

    An alternative could be to set the BackColor of the subform default to 9342606. Then you don't need the Case "", and it is a little bit more efficient, though you won't see the difference.

    But this is also OK.

    Imb.

    • Marked as answer by InnVis Sunday, August 13, 2017 12:38 PM
    Saturday, August 12, 2017 8:56 PM
  • Hi,

    We can certainly understand the frustration since we all went through the same pains early on. However, I do agree once you've learned the basics and dip your feet into deeper waters, you can see there are almost endless possibilities. So, my advice to you is to just keep on trying and never lose hope. When you run into a wall, someone here should be able to show you a door.

    Good luck with your project.

    • Marked as answer by InnVis Sunday, August 13, 2017 12:38 PM
    Saturday, August 12, 2017 9:07 PM
  • Oh, and this is the code of the save button on the edit form:

    Private Sub cmdSaveandNew_Click()
         mSaved = True
         DoCmd.Close
    End Sub

    and the double click event of the subform to open the edit form:

    Private Sub Form_DblClick(Cancel As Integer)
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmContactEdit"
        stLinkCriteria = "[AutoNum]=" & Me![txtAutoNUM]
        
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

    Hi Jamie,

    Modify the cmdSaveandNew_Click as follows:

    Private Sub cmdSaveandNew_Click()
      Dim prev_form As Form
      Dim cur_sub as SubForm
      Dim rs As Recordset
    
      mSaved = True
    
      Set prev_form = Forms("frmMain")
      Set cur_sub = prev_form.ActiveControl
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE AutoNUM = " & cur_sub!TxtAutoNUM)
    
      Vul_subform cur_sub, rs
    
      DoCmd.Close
    End Sub

    Take care. The above is air code, not tested, It can have some typos, but if you understand what is happening, you should be able to correct it.

    I am anxious to know the result.

    Good night, Imb.

    • Marked as answer by InnVis Sunday, August 13, 2017 12:37 PM
    Saturday, August 12, 2017 9:58 PM
  • Roger that, I'll make a new thread if I get stuck again! I guess this concludes this database. I'll work on placing it into our intranet as Leo suggested after I make some last minute cosmetic changes. By the way, the only reason I want to be able to export to excel is not to modify the data in the database at all, but our department needs to send a report to our higher ups with the entire hospital's recall roster so they can input it into another system they have. This is done monthly, so it saves my department hundreds of hours of data entry.

    Hi Jamie,

    That is great.

    A last remark: Service and ID in the frmMain are more or less application parameters. Users should not be able to modify the values, because they will be derived from the OpenArgs argument and the Subform that is selected.

    Success!!! Imb.

    • Marked as answer by InnVis Sunday, August 13, 2017 8:08 PM
    Sunday, August 13, 2017 7:32 PM

All replies

  • Hi,

    I think what you may need is to add a criteria to your recordset.

    So, you may have to change this:

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C")

    to something like this:

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE DeptID=" & Me.DeptID)

    Hope it helps...

    Friday, August 11, 2017 3:39 PM
  • Hey Leo, do you have any suggestions to shrinking this code? That way I don't have to define .ID=1, .ID=2, .ID=3, etc... for each record in loop?

    Me!sub01.Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=1"
        rs.MoveNext


    Also I changed the rs as suggested and I'm getting a "method or data member not found"

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE ID=" & Me.ID)

    • Edited by InnVis Friday, August 11, 2017 3:50 PM
    Friday, August 11, 2017 3:44 PM
  • 1. Yes, you can simplify the code by using an inner loop, and

    2. Make sure there is a control on your form for Me.ID.

    Here's an example for an inner loop:

    Do While Not rs.EOF
         For x = 1 To 67
              Me.Controls("Sub" & x).Form.RecordSource = "SELECT ... WHERE ID=" & x
         Next
         rs.MoveNext
    Loop

    Hope it helps...



    • Edited by .theDBguy Friday, August 11, 2017 3:58 PM
    Friday, August 11, 2017 3:54 PM
  • By a control on your form for Me.ID you're referring to the subform or the main form? The subform does have a hidden textbox named ID linked to the query with ID on it. Also there are 67 different subs, so don't I need to retype the sub name in a different line?

              Me.Controls("Sub" & x).Form.RecordSource = "SELECT ... WHERE ID=" & x

    • Edited by InnVis Friday, August 11, 2017 4:08 PM
    Friday, August 11, 2017 4:03 PM
  • Oh, I understand since x is 1 - 67 as long as all the subs are named Sub1, Sub2, Sub3, etc... then ("Sub & x) will automatically apply the command to Sub1-67!!! I'm getting a run-time error '3075' "Syntax error (missing operator) in query expression '...WHERE ID=1' This is what my entire code looks like now as opposed to my original HUGE code.
    Private Sub Form_Load()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C")
    
    Do While Not rs.EOF
        For x = 1 To 67
        
            Me.Controls("Sub" & x).Form.RecordSource = "SELECT...WHERE ID=" & x
            
        Next
        rs.MoveNext
    Loop
    
    End Sub
    


    • Edited by InnVis Friday, August 11, 2017 4:20 PM
    Friday, August 11, 2017 4:18 PM
  • Hi Alex,

    LOL. I was being lazy and used ellipsis to shorten the code I have to type since you have the full code anyway. I was just trying to demonstrate what you need to change.

    Hope it makes sense...

    Friday, August 11, 2017 4:24 PM
  • Getting a systems resources exceeded error for this code. If I remove the rs.Close line the database crashes completely >.<

    Private Sub Form_Load()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C")
    
    Do While Not rs.EOF
        For x = 1 To 67
    
            Me.Controls("sub" & x).Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=" & x
    Next
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
    End Sub

    Friday, August 11, 2017 4:44 PM
  • Hi,

    Let's try moving MoveNext like you had it in your original code. i.e.

    For x = 1 To 67

       Me.Controls...

       rs.MoveNext

    Next

    Hope it helps...

    Friday, August 11, 2017 4:49 PM
  • AHA! That worked, hehehe. Thanks Leo, that was awesome. Simplifying that HUGE code that made sense to me at the time, now seems like I have more possibilities with the format you provided.

    Do you have a way for me to get rid of the memory exceeded error if I add more subforms looped to the main form? I was hoping the shorter code would help.

    • Edited by InnVis Friday, August 11, 2017 5:30 PM
    Friday, August 11, 2017 4:55 PM
  • Hi Alex,

    Each time you set the record source of your subforms, you are making connections to the table, and Access can only handle so much connections at a time. One way to avoid using up resources is not make so many connections or close the ones you're not using anymore. There's a whole "science" behind trying to optimize a form to avoid getting the resources error.

    Good luck!

    Friday, August 11, 2017 5:05 PM
  • That makes sense, I can live with 67 records for now, heh.
    Friday, August 11, 2017 5:11 PM
  • Leo, when I add more records to the table using the inner loop, I get an error "no record found." I'm guessing I need to set some sort of line that specified if not EOF and x>67 then stop loop?

    Private Sub Form_Load()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C")
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do While Not rs.EOF
         For x = 1 To 67
    
    Me.Controls("Sub" & x).Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=" & x
        rs.MoveNext
        Next
            Loop
    Else
        rs.Close
        Set rs = Nothing
    End If
    End Sub

    Friday, August 11, 2017 5:30 PM
  • Private Sub Form_Load()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C")
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do While Not rs.EOF
         For x = 1 To 67
    
    Me.Controls("Sub" & x).Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID=" & x
        rs.MoveNext
        Next
            Loop
    Else
        rs.Close
        Set rs = Nothing
    End If
    End Sub

    Hi Jamie,

    Testing on rs.BOF is not necessary. In a new opened recordset the pointer is to the first record, or it is an empty set. BOF is only necessary in connection to MovePrevious.

    Internal in a loop I never use   Set rs = Nothing. In my long history and many applications I have never seen a signal that I needed that line.

    Further I think it is easier to name the first 9 subform controls Sub1, Sub2, ... instead of Sub01, Sub02, ... In that case you get a very simple loop:

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE DeptID = " & Me!DeptID)
    
      Do While (Not rs.EOF)
        x = x + 1
        Me("Sub" & x).Form.RecordSource = "SELECT * FROM qryContacts_3C WHERE qryContacts_3C.ID = " & x
        rs.MoveNext
      Loop
    
    End Sub

    Friday, August 11, 2017 6:04 PM
  • That makes sense, I can live with 67 records for now, heh.

    Hi Jamie,

    If the RecordSet has more records then there are subforms, then you can change the rs.MoveNext instruction in:

        If (x = 67) Then rs.MoveLast
        rs.MoveNext
    

    Imb.


    • Edited by Imb-hb Saturday, August 12, 2017 5:03 AM edit code
    Friday, August 11, 2017 6:10 PM
  • Hi Alex,

    If your table records are not divisible by 67, then I agree adding a check to make sure you don't exceed the recordcount may be necessary. However, if you'll notice Imb's code, he added the criteria I originally suggested earlier, so you can limit the number of records pulled for the form to only the ones you want to display.

    Friday, August 11, 2017 6:11 PM
  • Getting a systems resources exceeded error for this code. If I remove the rs.Close line the database crashes completely >.<

    Hi Jamie,

    The "systems resources exceeded"  error is because there are too many open Recordsets at the same time. In my systematics I never have that.

    What I understand now from your application is that it is used primarily for display purposes. Therefore the subforms do not need to be editable. A (dbl)click on the subform control could open an editable form with that record with only ONE recordset open.

    So you can make the subform unbound, that is no RecordSource. Instead you fill the controls of the subform with values form the looping recordset:

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE DeptID = " & Me!DeptID)
    
      Do While (Not rs.EOF)
        x = x + 1
        With Me("Sub" & x)
          !Control_1 = rs!Field_1
          !Control_2 = rs!Field_2
          ...
        End With
    
        rs.MoveNext
      Loop
    
    End Sub

    For Control_1, ... and Field_1, ...  you must substitute for real names.

    Now it is also very easy to change the backcolor of the subform control depending on some field value:

       ...
        Select Case rs!Dept
        case "USN": .Detail.BackColor = ...
        Case "USM": .Detail.BackColor = ...
        End Select
        ...

    I forgot the right names from the other thread, so I guessed some. Place this between the With and End With statements.

    Imb.

    Friday, August 11, 2017 6:41 PM
  • Having trouble with this part of the code. I event changed DeptID to the actual name of the field just ID, but I keep getting "can't find the field referred to in your expression."

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE DeptID = " & Me!DeptID)


    • Edited by InnVis Friday, August 11, 2017 7:07 PM
    Friday, August 11, 2017 7:05 PM
  • Hi,

    Can you post the SQL statement for your query qryContacts_3C? Thanks.


    Also, do you have a control on the main form named DeptID or ID? Is the main form bound or unbound?
    • Edited by .theDBguy Friday, August 11, 2017 7:12 PM
    Friday, August 11, 2017 7:12 PM
  • Having trouble with this part of the code. I event changed DeptID to the actual name of the field just ID, but I keep getting an invalid source error.

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE DeptID = " & Me!DeptID)

    Hi Jamie,

    If the actual name = "ID", then this should work:

            Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE ID = 3)

    or if ID is a text field:

            Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE ID = 'MyDepartment')

    But now your Department (where stands ID for?) is hardcoded. It is far more flexible to use a variable or a (hidden) control to store that variable value. Then you place the name of the variable outside the sql-string to get the value of the variable.

    Imb.

    Friday, August 11, 2017 7:16 PM
  • Main form is unbound, currently working on unbinding the subforms. The name of the field ID is the same for my query where the data is coming from and the field of the textbox that was displayed in my subform (I had this one invisible so I could refer to it, but didn't need to display that number).

    SELECT [Rank] & " " & [Last Name] AS [Contact Name], "O " & [Office Phone] AS [O Phone], "C " & [Mobile Phone] AS [M Phone], tblContacts_3C.[Other Phone], tblContacts_3C.[Job Title], tblContacts_3C.ID, [Directorate] & ", " & [Department] AS [Dir/Dep], tblContacts_3C.[Last Name], tblContacts_3C.[First Name], tblContacts_3C.[E-mail Address], tblContacts_3C.Branch, tblContacts_3C.Service, tblContacts_3C.Rank, tblContacts_3C.Group, tblContacts_3C.Directorate, tblContacts_3C.Department, tblContacts_3C.Notes, tblContacts_3C.Attachments, tblContacts_3C.[Office Phone], tblContacts_3C.[Mobile Phone], tblContacts_3C.Address, tblContacts_3C.City, tblContacts_3C.[State/Province], tblContacts_3C.[ZIP/Postal Code], tblContacts_3C.[Country/Region]
    FROM tblContacts_3C;
    

    Friday, August 11, 2017 7:16 PM
  • Hi,

    Thanks. Unfortunately, with the main form being unbound, it means you're probably not filtering the subforms to a specific Department. If this is the case, then you don't need to have a criteria in the code just yet. Just FYI, the "Me" keyword in your code refers to the main form and not the subform. So, using something like Me.ID or Me!ID will not work because the main form does not have an ID field or Textbox.

    Hope it makes sense...

    Friday, August 11, 2017 7:25 PM
  • Okay. So here's what I did:

    1). I removed the Record Source from my subform named subContactsDisplay_3C - this makes all the fields blank "unbound"

    2). I deleted all code from the subContactsDisplay_3C form

    3). I added the code into my main form named frmMain_3C (where all of my unbound subforms are)

    4). I set the Record Source to my frmMain_3C

    getting this error "item not found in this collection" what am I doing wrong?

    Private Sub Form_Load()
     Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE ID = " & Me!ID)
    
      Do While (Not rs.EOF)
        x = x + 1
        With Me("Sub" & x)
    
          !sub1 = rs!txtTitle
          !sub2 = rs!txtContactName
          !sub3 = rs!txtMPhone
          !sub4 = rs!txtOPhone
          !sub5 = rs!txtOtherPhone
          !sub6 = rs!txtBranch
          !sub7 = rs!txtID
    
        End With
    
        rs.MoveNext
      Loop
    
    
    End Sub
    Friday, August 11, 2017 7:45 PM
  • Main form is unbound, currently working on unbinding the subforms. The name of the field ID is the same for my query where the data is coming from and the field of the textbox that was displayed in my subform (I had this one invisible so I could refer to it, but didn't need to display that number).

    SELECT [Rank] & " " & [Last Name] AS [Contact Name], "O " & [Office Phone] AS [O Phone], "C " & [Mobile Phone] AS [M Phone], tblContacts_3C.[Other Phone], tblContacts_3C.[Job Title], tblContacts_3C.ID, [Directorate] & ", " & [Department] AS [Dir/Dep], tblContacts_3C.[Last Name], tblContacts_3C.[First Name], tblContacts_3C.[E-mail Address], tblContacts_3C.Branch, tblContacts_3C.Service, tblContacts_3C.Rank, tblContacts_3C.Group, tblContacts_3C.Directorate, tblContacts_3C.Department, tblContacts_3C.Notes, tblContacts_3C.Attachments, tblContacts_3C.[Office Phone], tblContacts_3C.[Mobile Phone], tblContacts_3C.Address, tblContacts_3C.City, tblContacts_3C.[State/Province], tblContacts_3C.[ZIP/Postal Code], tblContacts_3C.[Country/Region]
    FROM tblContacts_3C;

    Hi Jamie,

    If your tblContacts_3C has more records than you can display, then you will have to reduce the number by using a WHERE part in your sql-string, e.g. per department, or per floor, or per shift.

    If the number or records in tblContacts_3C is sufficient, then you do not need a WHERE part.

    Only when you have the same fieldname in more than one table, you need to specify the tablename, else you can omit it:

    SELECT [Rank] & " " & [Last Name] AS [Contact Name], "O " & [Office Phone] AS [O Phone], "C " & [Mobile Phone] AS [M Phone], [Other Phone], [Job Title], .ID, [Directorate] & ", " & [Department] AS [Dir/Dep], [Last Name], [First Name], [E-mail Address], Branch, Service, Rank, Group, Directorate, Department, Notes, Attachments, [Office Phone], [Mobile Phone], Address, City, [State/Province], [ZIP/Postal Code], [Country/Region]
    FROM tblContacts_3C;

    or in this case still easier:

    SELECT *,[Rank] & " " & [Last Name] AS [Contact Name], "O " & [Office Phone] AS [O Phone], "C " & [Mobile Phone] AS [M Phone], [Directorate] & ", " & [Department] AS [Dir/Dep] FROM tblContacts_3C;

    If you further try to avoid spaces and other "strange" characters in the fieldnames, you can also get rid of all those square brackets. Instead of [Last Name] , use  LastName or Last_Name.

    Imb.

    Imb.

    Friday, August 11, 2017 7:47 PM
  • Hi,

    As Imb said, let's try the following first to see if it would at least get rid of the error.

    Change the following line from this:

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE ID = " & Me!ID)

    to just this:

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C)

    We're taking out the criteria since your main form is unbound and probably does not have a control called "ID."

    You might still get an error, but it should be a different error this time.

    Let us know what happens...


    • Edited by .theDBguy Friday, August 11, 2017 7:52 PM
    Friday, August 11, 2017 7:52 PM
  • I understand where you're coming from Imb. Here's what happened:

    I initially wanted to keep things simple and make 1 separate table and separate form per department since I wanted maybe a maximum of 70-80 entries per department since those employees will stay there for a while and when they are gone, they will be removed from the recall roster and replaced with someone else. I then wanted to compile all the data in a master table for reporting purposes. I quickly realized as I was programing this that 1 table and 1 form per department was going to create a headache because there are A LOT of departments. So I came up with this idea that I can manually enter "ID" numbers 1 through x.... I think about 70 per department will be okay. So in the main form when I load depending on the use's account it will filter the form to his department. I already have the login portion setup so let's say user 1 = 3-Center (department), user 2 = 3-West (department), when the form loads only his staff would show... remember how I numbered record 1-x in the table? I was planning on assigning for example record 1-70 for department "3-Center," records 71-140 for department (4-Center) etc.... So each department has their own record, but all departments are typing into my single record without over writing each other or creating new entries (simply editing the ones I assigned to them).

    Friday, August 11, 2017 7:58 PM
  • Okay. So here's what I did:

    1). I removed the Record Source from my subform named subContactsDisplay_3C - this makes all the fields blank "unbound"

    2). I deleted all code from the subContactsDisplay_3C form

    3). I added the code into my main form named frmMain_3C (where all of my unbound subforms are)

    4). I set the Record Source to my frmMain_3C

    getting this error "item not found in this collection" what am I doing wrong?

    Private Sub Form_Load()
     Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE ID = " & Me!ID)
    
      Do While (Not rs.EOF)
        x = x + 1
        With Me("Sub" & x)
    
          !sub1 = rs!txtTitle
          !sub2 = rs!txtContactName
          !sub3 = rs!txtMPhone
          !sub4 = rs!txtOPhone
          !sub5 = rs!txtOtherPhone
          !sub6 = rs!txtBranch
          !sub7 = rs!txtID
    
        End With
    
        rs.MoveNext
      Loop
    
    
    End Sub

    Hi Jamie,

    Main form: unbound

    Each subform: unbound

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C ORDER BY ID")
    
      Do While (Not rs.EOF)
        x = x + 1
    
        With Me("Sub" & x)
          !control1 = rs!Title
          !control2 = rs![Contact Name]
          !control3 = rs![M Phone]
          !control4 = rs![O Phone]
          !control5 = rs![Other Phone]
          !control6 = rs!Branch
          !control7 = rs!ID
    
        End With
    
        rs.MoveNext
      Loop
    
    End Sub

     The recordset contains exactly the Contacts you want to display. Else you have to use a WHERE clause. The ORDER clause is necessary to get the contacts on the right place.

    Attention:  for control1, control2, etc. you must use the exact controlnames of the subform, and, on the right side, you must use the exact name of the field in the record, if necessary with the square brackets.

    Imb.

    Imb.

    Friday, August 11, 2017 8:10 PM
  • Oh thank God, it works flawlessly, you guys are insanely great. Love the mathematical mentality needed to put these together. Was getting scared there for a second, thing got real chaotic. LOL Now, how can I define the range of records for each department. For example in my table there is a field named "Service" that is a mandatory field when they double click the box and open the individual data entry form. I want to make it so records 1-67 are attached to Service "3-Center" records 68-134 are attached to Service 3-East, etc. Also, how can I make the unbound main form update after I close the data entry form. As it stands I need to close and reopen my main form in order to see updates added with the data entry form.
    • Edited by InnVis Friday, August 11, 2017 8:33 PM
    Friday, August 11, 2017 8:32 PM
  • I understand where you're coming from Imb. Here's what happened:

    I initially wanted to keep things simple and make 1 separate table and separate form per department since I wanted maybe a maximum of 70-80 entries per department since those employees will stay there for a while and when they are gone, they will be removed from the recall roster and replaced with someone else. I then wanted to compile all the data in a master table for reporting purposes.

    Hi Jamie,

    Things are becoming clearer.

    You can have all employees in one table, each with an indication in what Department he is in. You can use the same form for reporting using the Department to filter the contacts.

    If you use records 71-140 for department (4-Center), you have to translate back to the numbers in the subform names, and that is more complicated.

    Better is to use the numbers 1 - 70 for each department. Note that this nummers indicates in what position on the form the contact information is displayed. If you do not use a couple of numbers, then these subform will not be "populated".

    Imb.

    Friday, August 11, 2017 8:38 PM
  • Yes, but if I just keep records 1-70 then department A will overwrite department B since they have different employees. That's why I was thinking of 1 table with 800+ records max and assigning 70-100 records to individual departments. Department A owns record 1-100, department B owns 101-200, etc. Then based on their username and password when they log into the database, it will recognize the user as department A, B, C and will automatically filter the main form to their records (keep in mind there are only few designated people that can update this record, so you can say 1 login per department is all that is needed). The "Service" or department is unique to a cluster of individuals, say 100 records. Each record ID is unique to that employee (1, 2, 3...). In other words, when Department A logs in the first person on their screen is employee with ID "1" meaning he is the first record on the table, but when Department B logs in the first person on their screen is employee with ID "101"

    Should I just add 1 main form for each department and automatically program them to filter specifically?

    3-Center:

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C ORDER BY ID")
    
      Do While (Not rs.EOF)
        x = x + 1
    
        With Me("Sub" & x)
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
    
        End With
    
        rs.MoveNext
      Loop
    
    End Sub

    4-Center:

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C ORDER BY ID")
    
      Do While (Not rs.EOF)
        x = x + 72
    
        With Me("Sub" & x)
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
    
        End With
    
        rs.MoveNext
      Loop
    
    End Sub


    • Edited by InnVis Friday, August 11, 2017 8:56 PM
    Friday, August 11, 2017 8:48 PM
  • Also, how can I make the unbound main form update after I close the data entry form. As it stands I need to close and reopen my main form in order to see updates added with the data entry form.

    Hi Jamie,

    That is not too difficult too.

    In the Load event of the main form you fill (or refresh) the data in the subform for each record. Inside the loop there is a part where the controls of the subform are filled with values from the record (recordset).

    If you put this in a separate (generalized) procedure with the subform and the recordset as parameters, then you can use this procedure the refresh the current subform with the actual values form to current record. If this is a little too abstract, then show me your exact load procedure again in ordeer to give an actual solution.

    Imb.

    Friday, August 11, 2017 8:56 PM
  • Yeah it's still pretty abstract. My mind was gravitating towards an event on closing of the editing form, but if this code can be optimized, I'd love to see how it's rationalized. Also, where would you incorporate the "if (x=67) then" event?
      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C ORDER BY ID")
    
      Do While (Not rs.EOF)
        x = x + 1
    
        With Me("Sub" & x)
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
    
        End With
    
        rs.MoveNext
      Loop

    Friday, August 11, 2017 9:09 PM
  • Yes, but if I just keep records 1-70 then department A will overwrite department B since they have different employees. That's why I was thinking of 1 table with 800+ records max and assigning 70-100 records to individual departments. Department A owns record 1-100, department B owns 101-200, etc.

    Hi Jamie,

    No. Each employee has a number AND an indication in which Department he belongs. This Department can be an extra field in the Employee-table, or better a link to a Department_tbl (in that case the Employee record has a FK to the Department_tbl.

    With the right filtering on Department you will get the right employees:

         Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE Department = '" & current_department & "' ORDER BY ID")

    Imb.

    Friday, August 11, 2017 9:10 PM
  • I'm with Imb on this one. Consider just adding an extra field to designate the assigned department to avoid calculating which department the record belongs.

    This is what I originally thought you had when I first suggested adding a "WHERE DeptID=" & Me.DeptID to your SQL statement. It didn't work because you don't have a department field.

    Just my 2 cents...



    • Edited by .theDBguy Friday, August 11, 2017 9:18 PM
    Friday, August 11, 2017 9:16 PM
  • I think I understand what you're saying. As long as the data is consecutive and it is (ORDER BY ID) , it doesn't matter how the records are entered because it will always be filtered by department in order of ID.... So employee 1, 2, 3, 8, 55 can all be department A and they will filter the same in the subforms because it's numerical. I like it, but then the issue is, how do I fix my issue with more record and less subforms. I already have a department field named "Service" but I was not using it.

    • Edited by InnVis Friday, August 11, 2017 9:22 PM
    Friday, August 11, 2017 9:19 PM
  • Yeah it's still pretty abstract. My mind was gravitating towards an event on closing of the editing form, but if this code can be optimized, I'd love to see how it's rationalized. Also, where would you incorporate the "if (x=67) then" event?

    Hi Jamai,

    I will continue tomorrow. Only 6 hours left to sleep...

    Imb.

    Friday, August 11, 2017 9:21 PM
  • I think I understand what you're saying. As long as the data is consecutive and it is (ORDER BY ID) , it doesn't matter how the records are entered because it will always be filtered by department in order of ID.... So employee 1, 2, 3, 8, 55 can all be department A and they will filter the same in the subforms because it's numerical. I like it, but then the issue is, how do I fix my issue with more record and less subforms. I already have a department field named "Service" but I was not using it.

    Hi,

    Actually, the IDs don't even have to be consecutive. Imb's last sample code just puts the ID in order and then goes through each one without regard as to its value.

    Friday, August 11, 2017 9:26 PM
  • Thanks a ton Imb and Leo, please get some rest!
    Friday, August 11, 2017 9:33 PM
  • I see that because he made it scroll as x+1, am I right?
    Friday, August 11, 2017 9:34 PM
  • I see that because he made it scroll as x+1, am I right?

    Correct. Now, we don't care what the actual number is. The code would just go through each record one at a time.
    Friday, August 11, 2017 10:15 PM
  • Yeah it's still pretty abstract. My mind was gravitating towards an event on closing of the editing form, but if this code can be optimized, I'd love to see how it's rationalized. Also, where would you incorporate the "if (x=67) then" event?

    Hi Jamie,

    There I am again.

    You could make a Sub in a general module:

    Sub Fill_subform(cur_sub As Subform, rs As Recordset)
    
       With cur_sub
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
     
          Select Case rs!Branch
          Case "xxx": .Detail.Backcoler = ...
          Case "yyy": .Detail.Backcolor = ...
          End Select
    
       End With
    
    End Sub
    

    You have to substitute the right Branches and the right colors.

    Then the routine in the Load event reduces to:

      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C ORDER BY ID")
    
      Do While (Not rs.EOF)
        x = x + 1
        Fill_subform Me("Sub" & x), rs
    
        if (x = 67) Then rs.MoveLast
        rs.MoveNext
      Loop

    When a user has edited one record, then you can run the update for that subform by:

        Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE ID = " & current_ID)
        Fill_subform Me("Sub" & current_nr), rs

    In cases you want to modify the subform form, or the Contacts query, you only need to modify the Fill_subform routine once to tune things again.

    Imb.

    Saturday, August 12, 2017 5:31 AM
  • In cases you want to modify the subform form, or the Contacts query, you only need to modify the Fill_subform routine once to tune things again.

    Hi Jamie,

    With the use of the Fill_subform routine you have made a separation between WHAT is displayed and WHERE it is displayed. It is important to realize that, because that is the basic step to further automation.

    Now we have evolved so far, you have to think about the interrelation of the counter "x" and the employee-number per Department.

    Using the "x" places all contacts in the consecutive subform controls. Instead of x you can also use the employee-number. That number in the record then determines which subform will be filled. In my opinion that is a better way of working. The employee-number functions then as a placeholder on the form. The initial loop would then look like:

      Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE Department = " & ...)
    
      Do While (Not rs.EOF)
        Fill_subform Me("Sub" & rs!Emp_nr), rs
    
        rs.MoveNext
      Loop

    If you use this last way of looping, then you can also modify the Fill_subform routine to:      Fill_subform Me, rs

    Imb.

    Saturday, August 12, 2017 7:36 AM
  • Scratch that, my last question was dumb. The code works flawlessly, except when I use the "select" I get the error "method or data member not found."

    Sub Fill_subform(cur_sub As SubForm, rs As Recordset)
    
       With cur_sub
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
     
          Select Case rs!Branch
          Case "USN": .Detail.BackColor = 8388608
          Case "USA": .Detail.BackColor = 1007160
          End Select
    
       End With
    
    End Sub

    Also, where would I add the update routine?

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE ID = " & current_ID)
        Fill_subform Me("Sub" & current_nr), rs

    Do you think it would be a good idea to add an unbound combo box in the main form with the "service" names (i.e. 3-Center, 4-Center, MICU, SICU, etc...) and have the loop update the subforms to the records of the selected departments. So if 3-Center is chosen on the combobox, then it automatically displays only those employees in the right orders. It's important that the record remains in the original subform that the person imputing the data chose. If this was a possibility then I can use the onload event of the main form to automatically choose the value of the combobox and filter the main form, preventing departments from going into other departments. 


    • Edited by InnVis Saturday, August 12, 2017 3:27 PM
    Saturday, August 12, 2017 2:25 PM
  • Scratch that, my last question was dumb. The code works flawlessly, except when I use the "select" I get the error "method or data member not found."

    Hi Jamie,

    What was the last question?

    Does all things work as expected? Backcolors are automatically adapted? All subforms are being filled? You use now the ID to place the information on the right place?

    Can you elaborate on the exception of using the "select"?

    Imb.

    Saturday, August 12, 2017 3:43 PM
  • My original question was referring to the "Fill_subform" line, I didn't understand that function until I realized a separate general mod controlled it, my silly question was something along the lines of "what is my Fill_subform." Lol. The subforms are being filled fine, but the backcolors are not adapted the error I get is "method or data member not found." And I can't figure out how to use the ID to place the information on the right place. 

    This is what my code looks like on the main form:

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C ORDER BY ID")
    
      Do While (Not rs.EOF)
        x = x + 1
     Fill_subform Me("Sub" & x), rs
    
        If (x = 67) Then rs.MoveLast
        rs.MoveNext
      Loop
      
    End Sub
    
    and the general module (i put ' in front of the colors because I get the error above otherwise):
    Sub Fill_subform(cur_sub As SubForm, rs As Recordset)
    
       With cur_sub
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
     
    '      Select Case rs!Branch
    '      Case "USN": .Detail.BackColor = 8388608
    '      Case "USA": .Detail.BackColor = 1007160
    '      End Select
    
       End With
    
    End Sub
    

    Saturday, August 12, 2017 3:57 PM
  • Do you think it would be a good idea to add an unbound combo box in the main form with the "service" names (i.e. 3-Center, 4-Center, MICU, SICU, etc...) and have the loop update the subforms to the records of the selected departments. So if 3-Center is chosen on the <g class="gr_ gr_243 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" data-gr-id="243" id="243">dropdown</g>, then it automatically displays only those employees in the right orders. It's important that the record remains in the original subform that the person imputing the data chose. If this was a <g class="gr_ gr_471 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="471" id="471">posibiliy</g> then I can use the <g class="gr_ gr_496 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="496" id="496">onload</g> event of the main form to automatically choose the value of the <g class="gr_ gr_562 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" data-gr-id="562" id="562">dropdown</g> and filter the main form, preventing departments from going into other departments. 

    Hi Jamie,

    The above text is not Chinese, but almost the same hard to read.

    I think it is not a good idea to place a control on the main form where the user can select the department. In fact he can choose any, and rhat is not the intention.

    So you can better make some kind of authorization form, and on the basis of that the overview for form for the appropriate department is opened. The department name or department id can be passed to the overview form using OpenArgs.

    If you use the ID of the contact instead of the counter x, the subform to be used is preserved. Ordering of a recordset is not necessary any more.

    In the same routine where you open a form for editing, you can place the code to update the right subform information after succesful editing.

    Imb.

    Saturday, August 12, 2017 3:59 PM

  • In the same routine where you open a form for editing, you can place the code to update the right subform information after succesful editing.

    Imb.

    So on the double click event of the subforms or on the form that opens to edit? I'm sorry if I'm a bit of a noob in all of this. I'm a full-time Nurse that got frustrated by replicating data in multiple places over and over and over again. Compliance with maintaining records in my directorate are at an all time low and I got into access to hopefully make things easier. Everything I know is self-taught through articles, books, youtube videos, and dissecting demo databases. I really appreciate your time and Leo's time as it provides me invaluable knowledge.
    • Edited by InnVis Saturday, August 12, 2017 4:16 PM
    Saturday, August 12, 2017 4:11 PM
  • Private Sub Form_Load()
      Dim rs As DAO.Recordset
      Dim x As Integer
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C ORDER BY ID")
    
      Do While (Not rs.EOF)
        x = x + 1
     Fill_subform Me("Sub" & x), rs
    
        If (x = 67) Then rs.MoveLast
        rs.MoveNext
      Loop
      
    End Sub

    Hi Jamie,

    I think you missed one of my answers with a modification of the Load procedure:

      Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE Department = " & ...)
    
      Do While (Not rs.EOF)
        Fill_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
    
    

    I have assumed that already the information on the Department is passed to the form through OpenArgs.

    For test purposes you can simply edit the code to change the Department.

    All code that I type in is air code, so the debugger can not help. But now I see the problem, use:     .Form.Detail.BackColor

    Imb.

    Saturday, August 12, 2017 4:15 PM
  • Hmmm, I think I see the problem. I didn't add a department field to my subform just ID, Branch, Name, Office Phone, Mobile Phone, and Other Phone... I believe I have to add an invisible department field. By the way the department is referred to as "Service" in my query/table.
    Saturday, August 12, 2017 4:24 PM
  • Another silly question (>.<) ... I'm not really sure on what to add on the "..." of WHERE Service = " & ...)
    Saturday, August 12, 2017 4:31 PM
  • Another silly question (>.<) ... I'm not really sure on what to add on the "..." of WHERE Service = " & ...)

    Hi Jamie,

    ... means you have to fill in your own value, e.g. "3-East" or "4-Center".

    Imb.

    Saturday, August 12, 2017 4:47 PM
  • Hmmm, I think I see the problem. I didn't add a department field to my subform just ID, Branch, Name, Office Phone, Mobile Phone, and Other Phone... I believe I have to add an invisible department field. By the way the department is referred to as "Service" in my query/table.

    Hi Jamie,

    The "Service" field must be available in the Contacts query, to make the selection per Service.

    In my opinion it is not necessary in the subform itself, because it has the same value value for all subforms. You can use a control on the main form to store the value of the current Service.

    Imb.

    Saturday, August 12, 2017 4:53 PM
  • AHA!  So I would replicate the code per department, the question is how does the mainform know which department to load? or do I make a different main form per department?
    • Edited by InnVis Saturday, August 12, 2017 5:07 PM
    Saturday, August 12, 2017 5:03 PM
  • Private Sub Form_Load()
      Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE Service =" & "3-CENTER")
    
      Do While (Not rs.EOF)
     Fill_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
      
    End Sub
    error too few parameters
    Saturday, August 12, 2017 5:35 PM
  • AHA!  So I would replicate the code per department, the question is how does the mainform know which department to load? or do I make a different main form per department?

    Hmm, I don't think this is the intent. The intent of programming is to reuse code as much as possible. You can simply "filter" the recordset to a particular department, and the code should work as-is. There's no need to duplicate the code just as there was no need to duplicate the main form.

    Just my 2 cents...

    Saturday, August 12, 2017 5:36 PM
  • So on the double click event of the subforms or on the form that opens to edit? I'm sorry if I'm a bit of a noob in all of this. I'm a full-time Nurse that got frustrated by replicating data in multiple places over and over and over again. Compliance with maintaining records in my directorate are at an all time low and I got into access to hopefully make things easier. Everything I know is self-taught through articles, books, youtube videos, and dissecting demo databases. I really appreciate your time and Leo's time as it provides me invaluable knowledge.

    Hi Jamie,

    That is the same way as I started.

    And I got the same frustations within Access, when I started my second or thgird application. Copy-paste, copy-paste, and the same maintenance in all the different applications. Then I started to make generalized functionality.

    Today I make a new application in a couple of days, and modification or extension of an application is like polyclinic work.

    Most important is that you understand what you are doing, and that I understand what you understand.

    The problem with all this is that any answer should start with: "It all depends ...", ... but on what?

    Imb.

    Saturday, August 12, 2017 5:39 PM
  • Private Sub Form_Load()
      Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE Service =" & "3-CENTER")
    
      Do While (Not rs.EOF)
     Fill_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
      
    End Sub
    error too few parameters

    Hi Jamie,

    Is  Service a field in the query  qryContacts_3C?

    Besides of that, when the value is of the string-type, it should be enclosed with qoutes:    WHERE Service =" & "'3-CENTER'")

    Later, when you use a variable, this would become:    WHERE Service ='" & current_service & "'")

    Imb.

    Saturday, August 12, 2017 5:48 PM
  • Ok, I was missing the '' in "'3-CENTER'" so when I did this it filtered the data perfectly, but it didn't load the subs for records that did not have the service pre-set. So I have to define ahead of time that records ID 1-70 are all 3-CENTER and records 71-140 = 4-CENTER, etc. AFter I did that, it worked flawlessly.

    I'm still not able to have the main form update after data is entered/changed. Also, when I add a variable to my record to filter it, do I add it into the general module where the cur_sub is?

    • Edited by InnVis Saturday, August 12, 2017 6:20 PM
    Saturday, August 12, 2017 6:03 PM
  • Ok, I was missing the '' in "'3-CENTER'" so when I did this it filtered the data perfectly, but it didn't load the subs for records that did not have the service pre-set. So I have to define ahead of time that records ID 1-70 are all 3-CENTER and records 71-140 = 4-CENTER, etc. AFter I did that, it worked flawlessly.

    I guess the next question is... If on my onload event of the main form I have this code, then it will only show records for 3-CENTER. How do I make it so it only filters to the department pre-determined for the user or a dropdown, whatever is easier.


    Hi Jamie,

    No, as the ID now determines the place (or the subform) where the information is displayed, they always must fall in the range form 1 to the number of subforms. Each different Service gets its own 1-70(?) ID's.

    To select for a service, you can make a separate form where the user can select which Service he wants to address. After some authenciation and pressing a command button, the contacts form (main form) will be opened with Service passed through the OpenArgs argument.

    A different Service would pass a different value to the contacts form (main form), resulting in a different overview.

    Imb.

    Saturday, August 12, 2017 6:25 PM
  • When users are created in the database, the user is identified to a department in my tbl1UserAccounts and tbl2EmployeeTypes. Each department has an ID 1-18... 3-CENTER, 3-EAST, 3-WEST, 4-CENTER, etc... So when a user logs in, depending on what ID their account was registered under, my login form will perform a certain action. This is also how I enable and disable the shift open option. I also added a feature for users to change their own passwords by making the login form go invisible after you login instead of closing, so I can still refer to that user specifically. With that in mind, can I use these feature to automatically filter the main form instead of creating an entirely different filter criteria?
    Saturday, August 12, 2017 6:36 PM
  • OI'm still not able to have the main form update after data is entered/changed. Also, when I add a variable to my record to filter it, do I add it into the general module where the cur_sub is?

    Hi Jamie,

    On this moment it is far more important that the contacts overview (main form) works without any problem, and that the users can make their - legitimate - choice to see the contacts form. Data input can still be done in the way as has been done till so far.

    For the next step I still have a couple of questions about your understanding and about "it all depends ..."

    Each contact MUST have a Service field and an ID from 1 to about 70.

    Imb.

    Saturday, August 12, 2017 6:36 PM
  • Here's what the login code looks like. 

    Private Sub cmdLogin_Click()
        Dim rs As Recordset
        
        Set rs = CurrentDb.OpenRecordset("tbl1UserAccounts", dbOpenSnapshot, dbReadOnly)
        rs.FindFirst "UserName='" & Me.txtUserName & "'"
        
        If rs.NoMatch Then
            'Me.lblWrongUser.Visible = True
            MsgBox "The username you entered is invalid, please try again.", vbCritical, "INVALID USERNAME!"
            Me.txtUserName.SetFocus
            Exit Sub
        End If
        'Me.lblWrongUser.Visible = False
        
        If rs!PASSWORD <> Nz(Me.txtPassword, "") Then
            'Me.lblWrongPass.Visible = True
            MsgBox "The password entered is invalid, please try again.", vbCritical, "INVALID PASSWORD!"
            Me.txtPassword.SetFocus
            Exit Sub
        End If
        'Me.lblWrongPass.Visible = False
        
        If rs!UserAccessType = 18 Then
            Dim prop As Property
            On Error GoTo SetProperty
            Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)
            
            CurrentDb.Properties.Append prop
            
    SetProperty:
        If MsgBox("Would you like to turn on the bypass key?", vbYesNo, "ALLOW BYPASS") = vbYes Then
            CurrentDb.Properties("AllowBypassKey") = True
        Else
            CurrentDb.Properties("AllowBypassKey") = False
        End If
    
    End If
        If rs!UserAccessType = 18 Then
            DoCmd.OpenForm "frmStartShell_ADMIN"
        ElseIf rs!UserAccessType = 1 Then
            DoCmd.OpenForm "frmMain_3C"
        ElseIf rs!UserAccessType = 3 Then
            DoCmd.OpenForm "frmStartShell"
        ElseIf rs!UserAccessType = 1 Then
            DoCmd.OpenForm "frmStartShell_STUDENT"
        End If
        Me.Visible = False
        'DoCmd.Close acForm, Me.Name
        
    End Sub
    Instead of the last If "DoCmd.OpenFom" line can the filter be added here

    • Edited by InnVis Saturday, August 12, 2017 6:39 PM
    Saturday, August 12, 2017 6:38 PM
  • I see, alright so 1-70 has to repeat as long as the service is different.

    • Edited by InnVis Saturday, August 12, 2017 6:56 PM
    Saturday, August 12, 2017 6:43 PM
  •     If rs!UserAccessType = 18 Then
            DoCmd.OpenForm "frmStartShell_ADMIN"
        ElseIf rs!UserAccessType = 1 Then
            DoCmd.OpenForm "frmMain_3C"
        ElseIf rs!UserAccessType = 3 Then
            DoCmd.OpenForm "frmStartShell"
        ElseIf rs!UserAccessType = 1 Then
            DoCmd.OpenForm "frmStartShell_STUDENT"
        End If
    
    Instead of the last If "DoCmd.OpenFom" line can the filter be added here

    Hi Jamie,

    rs!UserAccessType = 1  has two occurances in the If-then-else statement. The second one thus will never occur.

    OpenArgs is the 7th argument in the OpenForm method.

    Try to open the form (frmMain_3C?) using:    DoCmd.OpenForm "frmMain_3C",,,,,,"3-East"

    In  the Load event you change the sql-string in:

        Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts_3C WHERE Service = '" & Me.OpenArgs & "'")

    See now what happens with the opened form. And what happens if you use:

         DoCmd.OpenForm "frmMain_3C",,,,,,"4-Center"

    Imb.

     

    • Marked as answer by InnVis Sunday, August 13, 2017 12:38 PM
    Saturday, August 12, 2017 7:07 PM
  • My apologies Imb, I just posted the code to show you my login form layout, the bottom section that sets the user access has not been completed then. Since I have limited skills in programming, my original idea was to create 1 separate form and 1 separate table per department. Since I am learning I do not need to do that since you guys helped me with a much much much better loop code, now I can afford to use only 1 table and 1 form for all departments. In this way my DoCmd.OpenForm will always be the same "frmMain" (notice I renamed it from frmMain_3C). So I'm hoping the login form will filter the frmMain" instead of opening a brand new form. 
    • Edited by InnVis Saturday, August 12, 2017 7:21 PM
    Saturday, August 12, 2017 7:20 PM
  • By the way, after I added the extra records to the table, the filtering on the main form works well, but my double click event on the subform to open up the edit form is broken. I think because I linked it to ID, but now the ID is not unique anymore. Is there a way to make the stLinkCriteria identify ID and Service as one?

    Private Sub txtContactName_DblClick(Cancel As Integer)
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmContactEdit"
        stLinkCriteria = "[ID]=" & Me![txtID]
        
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub
    • Edited by InnVis Saturday, August 12, 2017 7:37 PM
    Saturday, August 12, 2017 7:37 PM
  • omg this worked (the openargs) >.< that's amazingly cool
    • Edited by InnVis Saturday, August 12, 2017 7:52 PM
    Saturday, August 12, 2017 7:50 PM
  • Is there a way to make the stLinkCriteria identify ID and Service as one?

    Private Sub txtContactName_DblClick(Cancel As Integer)
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmContactEdit"
        stLinkCriteria = "[ID]=" & Me![txtID]
        
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

    How about something like?

    stLinkCriteria ="[ID]= & Me![txtID] & " AND Service='" & Me![txtService] &''"

    Make sure you use the correct control name.

    Or, if your table has an Autonumber field, you replace the [ID] with the Autonumber field, but you'll have to include it in your subform query/data.

    Hope it helps...


    • Edited by .theDBguy Saturday, August 12, 2017 8:44 PM
    Saturday, August 12, 2017 8:02 PM
  • I get an error typing the code "expected end statement"
    Saturday, August 12, 2017 8:07 PM
  • Is there a way to make the stLinkCriteria identify ID and Service as one?

        stLinkCriteria = "[ID]=" & Me![txtID]
        
    

    Hi Jamie,

    The record is now uniquely determined by  ID and Service. As the value of Service "sits" in the OpenArgs argument of the main form, you can use:

        stLinkCriteria = "ID = Me!txtID AND Service = '" & Me.Parent.OpenArgs & "'"

    You now see that the Contact record is uniquely determined by two fields: ID and Service, and for performance reasons it is advisable to use these fields in a key. Apart from this "natural" key you can also use an artificial key, e.g. an AutoNumber field. My strong preference goes to an Autonumber as Primary Key: the performance is better, and it can be automated. The value in the Autonumber field is not important, only that it is unique.

    So give your Contacts_tbl an AutoNumber field, e.g. Contact_id, and make this the PrimaryKey. This Contact_id must be included in the Contacts query.

    Now you can open the edit form using:

           stLinkCriteria = "Contact_id = " & Me!Contact_id

    On the subform form you can add a hidden control  "Contact_id", and set its value by adding a line in the Sub Vul_subform:

           !Contact_id = rs.Contact_id

    You can still follow this?

    Imb.

    Saturday, August 12, 2017 8:10 PM
  • Yup, it works beautifully. I went with the autonum method. Created an autonumber field in my tbl called AutoNUM. Then I made a hidden field in my subform named txtAutoNUM then I went ahead and added the AutoNUM to my query. I added the new criteria to my global mod and now the data filters beautifully, yay! I guess the only issue I'm still having and I know we've been over it, but it lost me: I can't get the data on the frmMain to update when I modify the information without having to close and reopen the form.
    • Edited by InnVis Saturday, August 12, 2017 8:21 PM
    Saturday, August 12, 2017 8:21 PM
  • Yup, it works beautifully. I went with the autonum method. Created an autonumber field in my tbl called AutoNUM. Then I made a hidden field in my subform named txtAutoNUM then I went ahead and added the AutoNUM to my query. I added the new criteria to my global mod and now the data filters beautifully, yay! I guess the only issue I'm still having and I know we've been over it, but it lost me: I can't get the data on the frmMain to update when I modify the information without having to close and reopen the form.

    Hi Jamie,

    You are sure EVERYTHING is working fine, with respect to the display of the different Services? Completely satisfied?

    The advantage of structured code (as important as structured data in databases!) is that with a few modifications you can tune the process flow.

    If you can show me the code for opening the edit form, and the code of the submit button of the edit form, then I can try to give a solution for automatic update of the subform control.

    Imb.


    Edit: With "brute force" you can close and re-open the overview form in the Close event of the edit form, but that is not fun.
    • Edited by Imb-hb Saturday, August 12, 2017 8:36 PM Edit
    Saturday, August 12, 2017 8:32 PM
  • Here's my login form:

    Private Sub cmdLogin_Click()
        Dim rs As Recordset
        
        Set rs = CurrentDb.OpenRecordset("tbl1UserAccounts", dbOpenSnapshot, dbReadOnly)
        rs.FindFirst "UserName='" & Me.txtUserName & "'"
        
        If rs.NoMatch Then
            'Me.lblWrongUser.Visible = True
            MsgBox "The username you entered is invalid, please try again.", vbCritical, "INVALID USERNAME!"
            Me.txtUserName.SetFocus
            Exit Sub
        End If
        'Me.lblWrongUser.Visible = False
        
        If rs!PASSWORD <> Nz(Me.txtPassword, "") Then
            'Me.lblWrongPass.Visible = True
            MsgBox "The password entered is invalid, please try again.", vbCritical, "INVALID PASSWORD!"
            Me.txtPassword.SetFocus
            Exit Sub
        End If
        'Me.lblWrongPass.Visible = False
        
        If rs!UserAccessType = 18 Then
            Dim prop As Property
            On Error GoTo SetProperty
            Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)
            
            CurrentDb.Properties.Append prop
            
    SetProperty:
        If MsgBox("Would you like to turn on the bypass key?", vbYesNo, "ALLOW BYPASS") = vbYes Then
            CurrentDb.Properties("AllowBypassKey") = True
        Else
            CurrentDb.Properties("AllowBypassKey") = False
        End If
    
    End If
        If rs!UserAccessType = 18 Then
            DoCmd.OpenForm "frmMain", , , , , , "3-CENTER"
        ElseIf rs!UserAccessType = 1 Then
             DoCmd.OpenForm "frmMain", , , , , , "3-WEST"
        ElseIf rs!UserAccessType = 2 Then
            DoCmd.OpenForm "frmStartShell"
        ElseIf rs!UserAccessType = 3 Then
            DoCmd.OpenForm "frmMain", , , , , , "4-CENTER"
    
    '.....
    
        End If
        Me.Visible = False
        'DoCmd.Close acForm, Me.Name
        
    End Sub

    Here's my main form's code (LOLOLOLOLOL, so much smaller than when I started this whole project):

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE Service ='" & Me.OpenArgs & "'")
    
      Do While (Not rs.EOF)
     Fill_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
    End Sub

    Here's the general module that sets the fields and codes (by the way the Case "" was not working so I had to go into the subform and make an onload event that if branch field was "" then make that color happen as a default which seems to work well):

    Sub Fill_subform(cur_sub As SubForm, rs As Recordset)
    
       With cur_sub
          !txtTitle = rs![Job Title]
          !txtContactName = rs![Contact Name]
          !txtMPhone = rs![M Phone]
          !txtOPhone = rs![O Phone]
          !txtOtherPhone = rs![H Phone]
          !txtBranch = rs!Branch
          !txtID = rs!ID
          !txtAutoNUM = rs!AutoNUM
          
          Select Case rs!Branch
          Case "": .Form.Detail.BackColor = 9342606
          Case "USN": .Form.Detail.BackColor = 8388608
          Case "USA": .Form.Detail.BackColor = 1007160
          End Select
    
       End With
    
    End Sub

    Saturday, August 12, 2017 8:41 PM
  • I get an error typing the code "expected end statement"
    Looks like I forgot a quote mark. Glad to hear you got it working using an Autonumber field. Cheers!
    Saturday, August 12, 2017 8:44 PM
  •  Created an autonumber field in my tbl called AutoNUM.

    Hi Jamie,

    If you want to develop a "housestyle", then you must think great. Imagine if you have 100's of tables, all with an AutoNumber Primary Key, How to distinguish all these different AutoNUM's. Probably the code can understand it, but do you understand what the query-string means?

    I have "standarized" the AutoNumber field on the item of the table: Contact_id for Contact_tbl, and this is completely to automate, But also is something like: IdContacts and tblContacts.

    But again, it is your application, your housestyle, your choice.

    Imb.

    Saturday, August 12, 2017 8:46 PM
  •       
          Select Case rs!Branch
          Case "": .Form.Detail.BackColor = 9342606
          Case "USN": .Form.Detail.BackColor = 8388608
          Case "USA": .Form.Detail.BackColor = 1007160
          End Select
    

    Hi Jamie,

    An alternative could be to set the BackColor of the subform default to 9342606. Then you don't need the Case "", and it is a little bit more efficient, though you won't see the difference.

    But this is also OK.

    Imb.

    • Marked as answer by InnVis Sunday, August 13, 2017 12:38 PM
    Saturday, August 12, 2017 8:56 PM
  • I have quickly learned that there are hundreds of ways to perform the same action in coding these programs with access. Higher understanding of the language gives the developer endless possibilities. My knowledge is still at an early level of novice that I don't really know my "style." I had a problem at work and I was inspired, so I sat down for hours and hours and sketched on paper what I wanted to happen (in a very broad way). When I sat down on the computer and tried making things happen, I got the large atrocity at the beginning of this post (LOL), because I don't fully understand how to tie things together yet, I write a line per action. Gives me a headache, but it's functional with my limited set. Sitting here the past 2-3 days with you guys, I have started to learn things I didn't even know existed, like a general module that the database can refer to. I also learned about displaying a record without actually pulling it and causing the computer system to freeze and sometimes crash like I was doing (now I can add more records for display). At this level, I am working harder and not smarter. I am strongly considering taking some formal classes to really understand concepts and language. The only reason I have been able to get as far as I have, again has been using logical, algebraic thinking, and of course dissecting and learning from demos and you guys.
    • Edited by InnVis Saturday, August 12, 2017 9:04 PM
    Saturday, August 12, 2017 9:00 PM
  • I did that, but for some reason the default kept getting changed to blue lol.
    Saturday, August 12, 2017 9:02 PM
  • Here's my main form's code (LOLOLOLOLOL, so much smaller than when I started this whole project):

    Private Sub Form_Load()
      Dim rs As DAO.Recordset
    
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE Service ='" & Me.OpenArgs & "'")
    
      Do While (Not rs.EOF)
     Fill_subform Me("Sub" & rs!ID), rs
    
        rs.MoveNext
      Loop
    End Sub

    Hi Jamie,

    It can even still smaller, especially when you have a need for Progress meters and Error handling. But that is for now out of scope.

    Imb.

    Saturday, August 12, 2017 9:03 PM
  • Hi,

    We can certainly understand the frustration since we all went through the same pains early on. However, I do agree once you've learned the basics and dip your feet into deeper waters, you can see there are almost endless possibilities. So, my advice to you is to just keep on trying and never lose hope. When you run into a wall, someone here should be able to show you a door.

    Good luck with your project.

    • Marked as answer by InnVis Sunday, August 13, 2017 12:38 PM
    Saturday, August 12, 2017 9:07 PM
  • Oh, and this is the code of the save button on the edit form:

    Private Sub cmdSaveandNew_Click()
         mSaved = True
         DoCmd.Close
    End Sub

    and the double click event of the subform to open the edit form:

    Private Sub Form_DblClick(Cancel As Integer)
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmContactEdit"
        stLinkCriteria = "[AutoNum]=" & Me![txtAutoNUM]
        
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

    • Edited by InnVis Saturday, August 12, 2017 9:16 PM
    Saturday, August 12, 2017 9:14 PM
  • Oh, and this is the code of the save button on the edit form:

    Private Sub cmdSaveandNew_Click()
         mSaved = True
         DoCmd.Close
    End Sub

    and the double click event of the subform to open the edit form:

    Private Sub Form_DblClick(Cancel As Integer)
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "frmContactEdit"
        stLinkCriteria = "[AutoNum]=" & Me![txtAutoNUM]
        
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

    Hi Jamie,

    Modify the cmdSaveandNew_Click as follows:

    Private Sub cmdSaveandNew_Click()
      Dim prev_form As Form
      Dim cur_sub as SubForm
      Dim rs As Recordset
    
      mSaved = True
    
      Set prev_form = Forms("frmMain")
      Set cur_sub = prev_form.ActiveControl
      Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE AutoNUM = " & cur_sub!TxtAutoNUM)
    
      Vul_subform cur_sub, rs
    
      DoCmd.Close
    End Sub

    Take care. The above is air code, not tested, It can have some typos, but if you understand what is happening, you should be able to correct it.

    I am anxious to know the result.

    Good night, Imb.

    • Marked as answer by InnVis Sunday, August 13, 2017 12:37 PM
    Saturday, August 12, 2017 9:58 PM
  • error highlighting vul_subform "Sub or Function not defined"

    Private Sub cmdSaveandNew_Click()
    Dim prev_form As Form
    Dim cur_sub As SubForm
    Dim rs As Recordset
         mSaved = True
    
    Set prev_form = Forms("frmMain")
    Set cur_sub = prev_form.ActiveControl
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE AutoNUM = " & cur_sub!txtAutoNUM)
    vul_subform cur_sub, rs
    
    DoCmd.Close
    End Sub
    I also tried Fill_subform cur_sub, rs instead of "vul_subform cur_sub, rs" and it sort of work, only if I reopen and resave the edit form. Not ideal, heh


    • Edited by InnVis Sunday, August 13, 2017 3:04 AM
    Saturday, August 12, 2017 10:19 PM
  • I also tried Fill_subform cur_sub, rs instead of "vul_subform cur_sub, rs" and it sort of work, only if I reopen and resave the edit form. Not ideal, heh

    Hi Jamie,

    Ofcourse it has to be "Fill_subform". My basic thinking was Dutch again on the late night.

    But I do not understand the rest of the sentence: and it sort of work ...

    If it is that frmMain does not display the modified data after edit record is closed, then you can try an additional line:

        Fill_subform cur_sub, rs
        prev_form.Refresh   

    Imb.

    Sunday, August 13, 2017 5:22 AM
  • Hey Imb, (is it silly I'm proud I figured out to try Fill?). I also tried prev_form.Refresh. No luck >.<
    Sunday, August 13, 2017 12:28 PM
  • omg, omg, omg, omg... I figured it out!!! I added the code into the on close event of the edit form and it worked!!!!!
    Private Sub Form_Close()
    Dim prev_form As Form
    Dim cur_sub As SubForm
    Dim rs As Recordset
         mSaved = True
    
    Set prev_form = Forms("frmMain")
    Set cur_sub = prev_form.ActiveControl
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE AutoNUM = " & cur_sub!txtAutoNUM)
    
    Fill_subform cur_sub, rs
    prev_form.Refresh
    End Sub
    • Edited by InnVis Sunday, August 13, 2017 12:37 PM
    Sunday, August 13, 2017 12:32 PM
  • Hi. Congratulations!
    Sunday, August 13, 2017 1:25 PM
  • omg, omg, omg, omg... I figured it out!!! I added the code into the on close event of the edit form and it worked!!!!!

    Hi Jamie,

    Congratulations! Are all your wishes now fulfilled? I think I'm gonna miss you.

    About your plans with formal classes ... You learn a lot about the very basics, but you hardly get answers to problems.

    You need basics, especially on Relational DataBase design, but with respect to Access it is in my opinion far more important to get practical experience, by solving problems, as you did. Learn to use the debugger, step through your code and try to understand what is really happening, and look up in the Help about the constraints of the (standard) routines. Then the next is using the forums, to ventilate your problem and to get ideas about how to solve it.

    What is your next problem?

    Imb.

    Sunday, August 13, 2017 1:35 PM
  • The last two things I'm missing should be much easier than this entire endeavor. All I need is a button to export the data I want from the entire database into an excel sheet and an unbound box on the top of my main form to display what service is currently being viewed. The rest is just "prettifying" this thing for my colleagues to use it, lol!

    EDIT: Oh, and I also have another form that acts like a master list called "frmContactList" for the person running reports to see everyone. My only concern with this form is that the very last line has the "new record" line for new records to be created. How can I disable this line, I'm afraid if this is messed around with, it will mess with the structure of the entire database.

    MISC. QUESTION: When this database is done, I could have the IT guys at work drop it into a share drive and people should be able to access it correct? No extra funny business coding for that right? God I hope not.

    • Edited by InnVis Sunday, August 13, 2017 2:36 PM
    Sunday, August 13, 2017 2:02 PM
  • MISC. QUESTION: When this database is done, I could have the IT guys at work drop it into a share drive and people should be able to access it correct? No extra funny business coding for that right? God I hope not.

    You would want to split your database into application and data files. Each user should have their own copy of the application file on their machine. Have a look at this Wiki article on how to properly share an Access database.

    Hope it helps...

    Sunday, August 13, 2017 3:32 PM
  • An intranet file is not an option? Usually we can drop an excel sheet into there and people can edit it and such.
    Sunday, August 13, 2017 3:35 PM
  • An intranet file is not an option? Usually we can drop an excel sheet into there and people can edit it and such.

    Yes, the backend (data) file will be in a Shared network location, and users (through the frontend application) can update the data at the same time. Using a single file to share among multiple users risks corrupting the data. Please read the article and let us know if you have any questions or concerns.

    Cheers!

    Sunday, August 13, 2017 4:53 PM
  • The last two things I'm missing should be much easier than this entire endeavor. All I need is a button to export the data I want from the entire database into an excel sheet and an unbound box on the top of my main form to display what service is currently being viewed. The rest is just "prettifying" this thing for my colleagues to use it, lol!

    EDIT: Oh, and I also have another form that acts like a master list called "frmContactList" for the person running reports to see everyone. My only concern with this form is that the very last line has the "new record" line for new records to be created. How can I disable this line, I'm afraid if this is messed around with, it will mess with the structure of the entire database.

    Hi Jamie,

    For various reasons it is better to start a new thread for every new question.

    On your frmMain you van add an additional control. In the Load event you can fill this control with the information that is available in OpenArgs,

    You can also add an CommandButton, e.g in the footer, to activate making an Excel-sheet. But be careful!!! Your goal was to start using structured data, that can only processed on one place: the database. By making Excel-outputs that can be easliy modified by the users you introduce the possibility that these sheets live their own lives. For easiness the users modify the sheet instead of the database. Next is the problem how to update the database from the changed Excel sheets. In principle data in Excel sheets lack the consistency and integrity that you can realize with a relational database.

    Export to Excel is ok, but in my opinion not for input of the modifications. For output you can also print the frmMain.

    Finally, in the frmContactList you can change the AllowAdditions property to False.

    Imb.

    Sunday, August 13, 2017 6:12 PM
  • Roger that, I'll make a new thread if I get stuck again! I guess this concludes this database. I'll work on placing it into our intranet as Leo suggested after I make some last minute cosmetic changes. By the way, the only reason I want to be able to export to excel is not to modify the data in the database at all, but our department needs to send a report to our higher ups with the entire hospital's recall roster so they can input it into another system they have. This is done monthly, so it saves my department hundreds of hours of data entry.
    Sunday, August 13, 2017 6:49 PM
  • Roger that, I'll make a new thread if I get stuck again! I guess this concludes this database. I'll work on placing it into our intranet as Leo suggested after I make some last minute cosmetic changes. By the way, the only reason I want to be able to export to excel is not to modify the data in the database at all, but our department needs to send a report to our higher ups with the entire hospital's recall roster so they can input it into another system they have. This is done monthly, so it saves my department hundreds of hours of data entry.

    Hi Jamie,

    That is great.

    A last remark: Service and ID in the frmMain are more or less application parameters. Users should not be able to modify the values, because they will be derived from the OpenArgs argument and the Subform that is selected.

    Success!!! Imb.

    • Marked as answer by InnVis Sunday, August 13, 2017 8:08 PM
    Sunday, August 13, 2017 7:32 PM
  • Yes sir, I understood this far! I went ahead and made those fields invisible and uneditable =)
    Sunday, August 13, 2017 8:08 PM