Record row to multiple textbox

[carl6885]

Hi

I am relatively new to VBA and I am gradually building my knowledge, know the basic of what most things do.

What I am trying to do now is take a row from a listbox after double clicking it and open another form with numerous textbox and populate them with the different column values from the textbox.

I guess this is similar to taking a row from a table and passing it to different textboxes and combo boxes, something I am not sure how to do.

I guess I could pass the ID from the column into the open args parameter of the new form then is it about populating a recordset/array and then distributing that across the control?????? Close???

Any help would be appreciated.

Thanks

Carl

 

[Mr. B]

I would suggest that you create a form bound to the record source for your data (table or query), including the controls that will display the data. Then on the double click event just read the record ID and pass that value in the Where parameter of OpenForm method. This will open the bound form to the specific record.

 

[carl6885]

Hi - thanks for your response - I would really like to do it without binding the form to record source as I want to know how to do it anagrammatically.

Is it achievable?

Thanks

 

[carl6885]

Found an example that can take values from a listbox and pass to another forms controls:

Dim strDocName As String

strDocName = "frmTest"
DoCmd.OpenForm strDocName
With Forms(strDocName)
.Caption = "Caption set from FormA"
.txt1 = Me.List16.Column(0)

I would appreciate it if anyone could share any other ways with more specifically around taking a row from a table and showing the fields in different controls on a form.

Thanks

Carl
End With

 

[Mr. B]

Ok. I do understand. I was just trying to provide you with the shortest distance between two points.

To programatically accomplish what you want you have at least a couple of choices.

Here is one option. Assuming that your listbox on your first form has all of the values you need to display on the second form, one option is to address each control in then second form providing the data from the selected record in the list box using vba statements in the Double Click event of your first form.

If you have a text box on your second form named "txtCoName" and in the list box you have two columns, one is the bound value of a record ID number and the second column in the list box is the Company Name and you want to populated the "txtCoName" control with the value from the Company Name column then your VBA statement would be:

Forms!NameOfForm2.txtCoName = Me.NameOfListbox.column(1)

Note that the column numbering of a listbox or combo box uses zero based numbering, meaning that the first column in the listbox or combo box is column 0. So if we need to read the value of the second column we use .column(1).

In the code above, juust replace the "NameOfForm2" with the name of your second form and the "NameOfListBox" with the name of your listbox.

You would need a statement like this for each control in the second form that you want to populate.

 

[carl6885]

Thanks for this Mr. B

Can you explain how I take a query/record and split it across controls on the form to edit and update the fields if needed?

Thanks

Carl

 

[Mr. B]

Carl,

They say that an example is the best tool to teach with so I will try to put something together that will show you how I would do it but I need to know what version of Access you are using.

 

[carl6885]

Mr. B, I am using Access 2010.

Thanks in advance.

 

[Mr. B]

Check out the attached demo file.

There are two forms, one with a list box to select the record and the other unbound form for viewing/editing the record.

There is VBA code that hopefully will give you some ideas.

Hope this helps.

 

[carl6885]

Mr. B

Thank you very much - exactly what I needed I now fully understand what is needed!

It comforting to know that I was on the right lines, just not hitting the mark.

Thanks again.

Carl