Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Unanswered: Struggling with VBA and dynamically filling a form

    I've got a form that displays a seating chart. The chart displays the person assigned to a given seat and each form shows graphically a given range of seats (like 1-25 or 26-80). I've got code that can populate the form with the first and last names of the people assigned to the seats and test to see if the seat is empty and if so, set the lblFirstName and lblLastName for that seat to visible = false. I'd like to do two things. First, I'd like to be able to do this with text boxes rather than labels so I can set the DisplayAsHyperlink to Always. Second, I'd like to set the onclick property for each text box to open a form with the record associated with FirstName or LastName. Elsewhere in my app I've got a continuous form that displays a list of the people. On that form I have a text box which is set visible = false and which contains the ID_Main value (the autonumber/key value) for the associated record. When I click the record the onclick has the following code:

    Code:
    Dim stConcat, stDocName As String
    
    stConcat = "ID_Main = " & Me.txtID.Value
    stDocName = "frmManipulateJuror"
    
    On Error GoTo Err_MainMenu_Click
    
        DoCmd.Close
        DoCmd.OpenForm stDocName, , , stConcat
    
    Exit_MainMenu_Click:
        Exit Sub
    
    Err_MainMenu_Click:
        MsgBox Err.Description
        Resume Exit_MainMenu_Click
    I'd like to have something similar happen when I click the name on my chart. On my chart I've got the labels names lblFirst0 and lblLast0 for seat 1, lblFirst1 and lblLast1 for seat 2 and so on. Right now the way I populate the lblFirstName and lblLastName is with the following code:

    Code:
        Dim recs As dao.Recordset
        Dim strSQL As String
        Dim intOffset As Integer
        Dim lngSeatCounterNum As Long
        
        lngSeatCounterNum = 25 - 1
        
        strSQL = "SELECT tblMain.FirstName, tblMain.LastName, tblMain.Seat " _
            & "FROM tblMain " _
            & "WHERE (((tblMain.Seat) Is Not Null) " _
            & "AND ((tblMain.CaseNum)='25CBV0609') " _
            & "AND ((tblMain.Seat) Between 1 And 25)) " _
            & "ORDER BY tblMain.Seat;"
        
        Set recs = CurrentDb.OpenRecordset(strSQL)
        Dim stFirst, stLast As String
        With recs
            For I = 0 To lngSeatCounterNum
                Forms(strMyFormName).Controls("lblFirst" & I).Caption = "0"
            Next
            If .BOF And .EOF Then
                'do nothing here
            Else
                intOffset = lngLowSeat
                Do While Not .EOF
                    Forms(strMyFormName).Controls("lblFirst" & (!Seat - intOffset)).Caption = !FirstName
                    Forms(strMyFormName).Controls("lblLast" & (!Seat - intOffset)).Caption = !LastName
                    .MoveNext
                Loop
            End If
            
            For I = 0 To lngSeatCounterNum
                If Forms(strMyFormName).Controls("lblFirst" & I).Caption = "0" Then
                    Forms(strMyFormName).Controls("lblFirst" & I).Visible = False
                    Forms(strMyFormName).Controls("lblLast" & I).Visible = False
                Else
                    Forms(strMyFormName).Controls("lblFirst" & I).Visible = True
                    Forms(strMyFormName).Controls("lblLast" & I).Visible = True
                End If
            Next
        End With
        Set recs = Nothing
    I've tried replacing

    Code:
    .Caption = "0"
    with

    Code:
    .ControlSource = "=0"
    and that seemed to work, but when I tried replacing

    Code:
    .Caption = !FirstName
    with

    Code:
    Dim stFirstHolder, stLastHolder AS String
    
    stFirstHolder = "= '" & !FirstName & "'"
    stLastHolder = "='" & !LastName & "'"
    
    ...
    
    .ControlSource =  stFirstHolder
    .ControlSource = stLastHolder
    that didn't work... it complained about not supporting that method or something. Is there a way to accomplish all of this? I'm stumpped. Thanks for your help!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Forms(strMyFormName).Controls("tboxFirst" & (!Seat - intOffset)) = !FirstName
    BTW, if you have this code running in strMyFormName it will run faster if you avoid talking to the Forms! collection every time i.e.
    me.Controls("tboxFirst" & (!Seat - intOffset)) = !FirstName

    why .controlsource (and why = "=0") ?
    - the form is (should be) unbound and has no .controlsource
    - since you already have the index of captions "0" you can use this to hide/not texboxes with the same index

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Eureka! That's so simple... and works like a charm! Thanks so much. So... only question that remains: is it possible to set the onclick within the same loop?

    BTW... the reason for the "Forms.()" use is because I store this in a module and call it from a number of forms. I guess it's a tradeoff... as a side note... I just assumed it was better to put code that is called from a number of forms in a module... but is there some way of determining when its better to repeat code on each form rather than place it in a module?

    About the rest, I didn't know i could leave off the .controlsource, and I didn't know I could set the txtbox to 0 without anything else. Learnin' new things all the time.

    Thanks again!

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You should be able to do the OnClick with the Tag property of the textbox. Just set the Tag property to whatever parameters you need for the calling function. Maybe it is the UserID or something like that. Then for each Textbox add something like:

    ProcedureName Me.txtWhatever.Tag

  5. #5
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    thanks for your response... unfortunately, I'm confused. I tried playing with the .onclick and it didn't work the way I wanted it to. Here's the code I've got right now (with my unsuccessful additions highlighted in blue):

    Code:
    Sub ShowMeTheSeats(lngLowSeat, lngHighSeat As Long, strMyFormName, strMyCaseNum As String)
        Dim recs As dao.Recordset
        Dim strSQL, stConcat As String
        Dim intOffset As Integer
        Dim lngSeatCounterNum As Long
        
        lngSeatCounterNum = lngHighSeat - lngLowSeat
        
        strSQL = "SELECT tblMain.FirstName, tblMain.LastName, tblMain.Seat, tblMain.ID_Main " _
            & "FROM tblMain " _
            & "WHERE (((tblMain.Seat) Is Not Null) " _
            & "AND ((tblMain.CaseNum)='" & strMyCaseNum & "') " _
            & "AND ((tblMain.Seat) Between " & lngLowSeat & " And " & lngHighSeat & ")) " _
            & "ORDER BY tblMain.Seat;"
        
        Set recs = CurrentDb.OpenRecordset(strSQL)
        Dim stFirst, stLast As String
        With recs
            For I = 0 To lngSeatCounterNum
                Forms(strMyFormName).Controls("txtFirst" & I) = "0"
            Next
            If .BOF And .EOF Then
                'do nothing here
            Else
                intOffset = lngLowSeat
                Do While Not .EOF
                    stConcat = "txtID" & (!Seat - intOffset)
                    Forms(strMyFormName).Controls("txtID" & (!Seat - intOffset)) = !ID_Main
                    Forms(strMyFormName).Controls("txtFirst" & (!Seat - intOffset)) = !FirstName
                    Forms(strMyFormName).Controls("txtFirst" & (!Seat - intOffset)).OnClick = OpenFormAndClosePrevious("frmManipulateJuror", stConcat)
                    Forms(strMyFormName).Controls("txtLast" & (!Seat - intOffset)) = !LastName
                    Forms(strMyFormName).Controls("txtLast" & (!Seat - intOffset)).OnClick = OpenFormAndClosePrevious("frmManipulateJuror", stConcat)
                    .MoveNext
                Loop
            End If
            
            For I = 0 To lngSeatCounterNum
                If Forms(strMyFormName).Controls("txtFirst" & I) = "0" Then
                    Forms(strMyFormName).Controls("txtFirst" & I).Visible = False
                    Forms(strMyFormName).Controls("txtLast" & I).Visible = False
                Else
                    Forms(strMyFormName).Controls("txtFirst" & I).Visible = True
                    Forms(strMyFormName).Controls("txtLast" & I).Visible = True
                End If
            Next
        End With
        Set recs = Nothing
    End Sub
    and then the OpenFormAndClosePrevious looks like this:

    Code:
    Public Sub OpenFormAndClosePrevious(stDocName As String, Optional stLinkCriteria As String, Optional stArgPasser As String)
    On Error GoTo Err_MainMenu_Click
    
        DoCmd.Close
        DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stArgPasser
    
    Exit_MainMenu_Click:
        Exit Sub
    
    Err_MainMenu_Click:
        MsgBox Err.Description
        Resume Exit_MainMenu_Click
    
    End Sub
    Ultimately I'd like to pass the name of the form and the link criteria to OpenFormAndClosePrevious but I need to assign a different stconcat for each link. What am I doing wrong? Thanks for your help!

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    First of all you need to assign a string to the OnClick property. I would try somthing like:

    ="=OpenFormAndClosePrevious('frmManipulateJuror '," & stConcat & ")"
    or
    ="OpenFormAndClosePrevious('frmManipulateJuror' ," & stConcat & ")"


    If that doesn't work then, what I proposed was to set the Tag property in the ShowMeTheSeats, and then in the form for each OnClick add:

    OpenFormAndClosePrevious Me.txtBox.Tag

    If you have more than one parameter, you can parse a string in the Tag that is delimited by commas or ~ or some other character. You can use the Split function to help with that.

  7. #7
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Wow! That's so cool... Thanks... using .Tag did the trick. That's Awesome! Problem Solved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •