Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013

    Post Unanswered: Passage of parameters - Form Update

    Hi all.
    I have been looking around to solve my little issue, but I haven't been that lucky so far, so I decided to write a post. I know some VBA and did some simple programming. This one is beyond my capabilities, although it may seem the simplest thing to some of the readers.

    The DB I am working on concerns a terminology database (termbase) (Definition). The labels are in Italian, but I provide suitable English translation here.

    I have two tables in the DB: scheda (card) and domini (domains). Every single term belongs to at least one domain. Domains are indicated by a three-character code and one definition (DB relations here).

    Problem: as I input a new record (i.e. a new term), I want to assign it to the domain(s) it belongs to. For example: "aircraft" may belong to "air force" and "transportation".

    What I want to do is the following: when I click on the "Assegna dominio" (assign to domain) button in the "scheda" (term card) Form, the Domains Form opens up (screenshot).

    After I flagged what domains the term belongs to, I click on the "Assegna ed esci" button (Assign and Exit) (name of button in code is "aggiungi_domini").
    The sub goes through the entire Domains Form (it's about 200 records) and creates a string by appending flagged domains (screenshot). The string is then appended to the content of the [dominio] field (domain) in the "scheda" (term card) Form (that's where parameters are passed).

    Here's the code I have written so far:

    Public Function aggiungi_domini_click(ByVal addme As String)
    Dim i As Integer, domstr, titolo As String
    i = 1
    domstr = ""
    titolo = "Domain"
    DoCmd.GoToRecord , "domini", acFirst            'go to record #1 in Domini form
    Do Until i = DCount("*", "domini")              'from beginning to end of table
      If add_flag = True Then                       ‘check if flag is set to APPEND
        If i = DCount("*", "domini") Then Exit Do   'if end of table, exit
        domstr = domstr & [codice_dominio] & " "    'else append domain
        add_flag = False                            'reset flag to NOT APPEND
        i = i + 1                                   'increment counter
        DoCmd.GoToRecord , "domini", acNext         'move to next record
      Else                                          'flag is set to NOT APPEND
        If i = DCount("*", "domini") Then Exit Do   'if end of table, exit
        i = i + 1                                   'increment counter
        DoCmd.GoToRecord , "domini", acNext         'go to next record
      End If
    addme = domstr
    End Function
    Public Sub apri_domini(ByVal domstr As String)
      DoCmd.OpenForm "domini", acNormal
      With Forms("domini").Controls("aggiungi_domini")
        If .OnClick = "" Then
        .OnClick = "aggiungi_domini_click addme:=domstr"
        End If
      End With
    DoCmd.Close acForm, "domini", acSaveYes         'close the form
    End Sub
    Private Sub assegna_dominio_Click()
    apri_domini domstr:=dominio
    End Sub
    Probably (perhaps definitely) my code has some faults I cannot see, given my elementary knowledge.
    Can anyone help?
    Thank you in advance.
    Last edited by altotoe; 04-23-13 at 09:12.

  2. #2
    Join Date
    Sep 2005
    G'd afternoon,
    In my opinion you need a third table to store the multiple domains for each one of your cards. You need a many-to-many relation. A Card may have many domains and a domain many cards.
    The reason i think you may want to take this path, is because as your db grows searches based on keywords stored as a single string will take forever or just impossible.
    My approach would be to create a new table with its own PK and 2 FK's and relate to both tables. Declare a Public Variable in a module to store the textbox containing the "domini" string for example:

    [COLOR="Blue"]Public pctlTxtKeywords As TextBox

    then in your form "Scheda" behind the "Assegna dominio" click event i will have something like this
    Private Sub btnOpen_Click()
    On Error GoTo Err_btnOpen_Click
        Set pctlTxtKeywords = Me.txtKeywords
        DoCmd.OpenForm "frmPopUp"
        Exit Sub
        MsgBox Err.Description
        Resume Exit_btnOpen_Click
    End Sub
    Finally i would build a pop up form with two controls: 1 listbox and 1 button
    The listbox with a recordsource based on some fields from the "Domini" table, multiselect option: Extended.
    The button to process the the whole task. For the sake of simplicity i won't get in to details about the listbox colums, enough to say it has the three columns from the 3rd table. The code will look like this

    Private mstrKeywords As String
    'This part of the query is constant 
    Private Const mcstrAppend As String = "INSERT INTO tblSchedaDomini (FKScheda,FKDomini) SELECT "
    Private Sub Form_Open(Cancel As Integer)
    On Error Resume Next
       'this line is to set the get the previous values (if exist) from the keywords field in the main form
        mstrKeywords = pctlTxtKeywords
    End Sub
    Private Sub btnApply_Click()
    On Error GoTo ExceptionHandler
        Dim cmdAppend       As New ADODB.Command
        Dim varItem         As Variant
        Dim strValues       As String
        Dim strQry          As String
        Dim t               As Integer
       'Set the command text object
         With cmdAppend
            .ActiveConnection = CurrentProject.Connection
            .CommandType = adCmdText
         End With
        'check for selected items
        If Me.lbxOrders.ItemsSelected.Count > 0 Then
               'Loop to get all items selected
                 For Each varItem In Me.lbxOrders.ItemsSelected
                'Get the row index
                    t = VBA.CInt(varItem)
                   'Contacenate the values to append like: 4,5) NOTE that the other parentesis is already in the constant
                    strValues = Me.lbxOrders.Column(0, t) & "," & Me.lbxOrders.Column(2, t)
                   'Concatenate the values for presentation puroposes
                    mstrKeywords = mstrKeywords & "+" & Me.lbxOrders.Column(1, t)
                   'Build the Query like: Append to tblSchedaDomini (FKScheda, FKDomini) Values (4,5)
                    strQry = mcstrAppend & strValues
                    cmdAppend.CommandText = mcstrAppend & strValues
                   'Execute the command
         End If
       Set cmdAppend = Nothing
       Access.DoCmd.Close acForm, Me.Name
        Exit Sub
            MsgBox "Exception found " & Err.Number & VBA.vbCrLf & "Description: " & Err.Description
           'Add code here to handle the exception
             Resume Exit_Here
    End Sub
    Private Sub Form_Close()
        On Error Resume Next
       'to avoid the forms!form("frmwhatever").thiscontrol etc... i just declared a public variable for that unique txtbox
       pctlTxtKeywords = mstrKeywords
    End Sub
    Here is a little demo based on some of the Northwind Db
    Attached Files Attached Files
    Last edited by Estuardo; 04-19-13 at 20:57.

  3. #3
    Join Date
    Apr 2013
    Estuardo, thank you for your invaluable answer. I am not sure I have got everything you have writte, but this is due to my poor knowlege of the matter. On the contrary, your suggestion are indeed clear and concise. The three-table solution is perhaps the best. As per the code, it will take me some time to "decode" it but I will definitely succeed. Thanks God, there are people like you out there, who can teach novice people to walk the right path and how.
    I will definitely come back to you and the forum as soon as I implement the correct procedure.
    Again, thank you for the contribution!

  4. #4
    Join Date
    Apr 2013

    Still a small error

    I am sorry if I have to ask for some clarifications again, but I get an error I don't understand concerning the name of method.
    For the sake of clarity and to avoid any misunderstanding, I have just attached the entire db.
    The only button in the 'scheda' form (upper right) should popup the popup form, but that's where I get the error.

    I have checked the two code listings in your sample db and mine and they look exactly the same (apart from the field and tables names, of course).

    Any clue?
    Attached Files Attached Files

Posting Permissions

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