Copy multiple data from Cbobox to Textbox (1 Viewer)

JPR

Registered User.
Local time
Yesterday, 23:07
Joined
Jan 23, 2009
Messages
185
Hello,
I have a form with a combo and a textbox.
The combo has its records source to a table named documents. The text box is unbound.
I was wondering if if would be possible to copy different records (not at the same time) from the combo box and place them in new lines in the text box. Something like this:

ID
Passport
Driving License

Thank you
 

Ranman256

Well-known member
Local time
Today, 02:07
Joined
Apr 9, 2015
Messages
4,102
the query behind the combo box should have 3 columns
the combo property: column count= 3, either hide them or show them. (via columnwidths)
then in the AFTERUPDATE of the combo, fill the text boxes.

Note: in VB, columns begin with zero

Code:
sub cboBox_Afterupdate()
txtID = cboBox.column(0)
txtPasspt = cboBox.column(1)
txtDriverLic = cboBox.column(2)

end sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:07
Joined
Feb 19, 2002
Messages
36,296
If the text box is unbound, the data won't be saved. And if it were bound, you would be mushing multiple attributes into a single field and that causes other problems. If you want to associate multiple documents to a record, create a separate table and use a subform.
 

JPR

Registered User.
Local time
Yesterday, 23:07
Joined
Jan 23, 2009
Messages
185
Hello,
as recommended, I have created a new table. Unfortunately I am now having a problem when adding a New Record to my DB. This only happens the first time, when there are no other records in the db.
I am sharing a test copy of my database. It is used to keep track of document we receive from clients and that need to be returned to them via courier.

Once you open the file, click on New Case and a form will open that will allow users to add a new case. When you click on the button Save/Close, you will notice that the list box on the Main Menu has two records.
By double clicking on a records, it will open the same form for any updates.
I would really appreciate your help as I do not know how to fix this problem.
Thank you.
 

Attachments

  • Test.zip
    58.6 KB · Views: 46

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:07
Joined
Feb 19, 2002
Messages
36,296
You don't need to use an append query. Just use a bound form. The reason you are getting two records is because you are saving the current record and then appending a new, empty one. You need to modify the log table to remove all the "doc" fields. All of those belong as separate rows in a doc table. Use a subform to add the log records. Replace the unbound control with the subform. Add the type to the subform so that you can tell which documents are which type.

Add a button to give the user a dialog to find the file he wants to log. I've included the code. The commented out lines show alternative code that you might want to use. The code in the button also follows. The name I am using for the control to hold the file name including the path is txtInputFile. It should be bound to a field in the table that the New Case form is bound to.

Code:
Private Sub cmdBrowse_Click()

    On Error GoTo ErrProc
    
    Me.txtInputFile = fChooseFile()
    If Me.txtInputFile & "" = "" Then
        MsgBox "Please select a file", vbOKOnly
        Exit Sub
    End If
    
ExitProc:

    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 3078
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub

Code:
Public Function fChooseFile()
 
   ' Requires reference to Microsoft Office xx.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
 
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
            
      ' Set the title of the dialog box.
      .Title = "Please select one file"
 
      'starting location
      .InitialFileName = CurrentProject.Path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
''''      .Filters.Add "Access Databases", "*.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next       
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Function
 

JPR

Registered User.
Local time
Yesterday, 23:07
Joined
Jan 23, 2009
Messages
185
Hello Pat.
First le me thank you for your help.
I have removed the DOC fields in the LOG table but am confused on what control rename txtinputfile.

I have tried to add your codes to my sample, removed the unbound listbbox and replace it with a subform which has its records source to the DOCS table but just cannot get it to work (probabaly a bit too advanced for my knowledge).
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:07
Joined
Jul 9, 2003
Messages
14,515
I was wondering if if would be possible to copy different records (not at the same time) from the combo box and place them in new lines in the text box.

I did a video demonstration how to do this, see Here:-

Get Info From a Combo-box 1 - Nifty Access​

 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:07
Joined
Feb 19, 2002
Messages
36,296
There is too much wrong with the app for me to fix it for you.
1. You need only one form that handles both adds and changes. Using separate forms just makes validation more difficult because you would have to duplicate it in both forms and then maintain it in two forms. Too much room for error. Just use a single form. If you have a condition that needs to be different for add and change, use Me.NewRecord to determine if this is a new or existing record.
2. the LogDoc table is the start of what you need to log documents but it needs additional information such as the LogID Don't name all autonumbers "ID". It just adds to confusion. Putting the LogID into the LogDoc table allows you to link the document to the log record. You also need a field to hold the path. Make the length 255. If you are inclined to use very long path/file names, you will need to use a long text field instead of a short text field. You probably want to move the Note field AND Received field from Log to LogDoc since they relate to the document, NOT the user.
3. Remove all the Doc fields from the Log table. Each logged document will become a separate row in LogDoc.
4. Create a subform for the LogDoc data and add it on the Case form. Replace the unbound field with the subform. Make sure the master/child links are set correctly. (It is always best to start from day 1 with defining relationships and enforcing RI as you add each new table to the schema.
5. Put the code I suggested in the new subform. There will be a button to open the file dialog and save the selected file. You can also default the Received date to Date() in this code. i would lock the Received field and not allow changes to it. Documents should be logged as they are received so this shouldn't be a problem

Take a stab at that and get back to us.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:07
Joined
Jul 9, 2003
Messages
14,515
3. Remove all the Doc fields from the Log table. Each logged document will become a separate row in LogDoc.

I haven't looked at your database, I'm just guessing from what @Pat Hartman has written. I have a "Normalisation Tool" (a transposition tool) which can take an incorrect structure, (like having Fields, where each field represents data) and should be row(s) in a table. I think Pat is telling you this.

Details about my normalisation tools are on my website here:-


If you think it will be suitable, contact me by this websites private messaging system and I will explain how you can get a free copy...
 

Users who are viewing this thread

Top Bottom