DoCmd.OpenForm issue

[chris-uk-lad]

Hi,

I have a listbox in a form that displays some info in columns (lstNotes). The fourth column contains text that is too large for the listbox so im making it display the full text in a seperate form in a textbox once clicked. I however cant seem to get this to work.

Private Sub lstNotes_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmNote", acNormal, "", "[TEXT] = " & Me!lstNotes, acFormEdit, acNormal
End Sub

frmNote is the new form, with a textbox called txtNote. lstNotes contains PAGE, DATE, NAME, TEXT columns retrieved from a table.

Any other info needed please ask

 

[Poppa Smurf]

Change "[TEXT] = " & Me!lstNotes to the following format.

[fieldname] = Forms![formname]![controlname on other form].

Personally I would do the following, when you click on the list box.

1. Open the form frmNote.
2. On the Open Event of the form put the following line of code
Me!text=Forms![formname]![controlname on other form].

I know that this is similar to the above code.

Poppa Smurf

 

[DCrake]

On the OnLoad of your form place similar code to this

Me.BigTextBox = Forms("CallingForm")("ListBox").Column(x)
Me.BigPage = Forms("CallingForm")("ListBox").Column
Me.BigName = Forms("CallingForm")("ListBox").Column(z)

Remember to replace my aircode with actual names and number in your app.

 

[chris-uk-lad]

Thanks for the help, i have it working .

A question for future reference, if i didnt have TEXT in the first column, how would i specify that its text that i want displaying? Forgive me if im wrong but appears only the first column is displayed in this click method.

Thanks again

 

[DCrake]

Not quite sure where you are going? can you provide an example...

 

[Poppa Smurf]

If it is in another column then you would use
Forms("CallingForm")("ListBox").Column. where y is the column number. Remember the first column in the list box is column(0), the second column is column(1) etc.

 

[chris-uk-lad]

Ok well i have a form which reports on a table, so you type in a number, click search, and the record is displayed. This record contains a document page, date, name and comment (TEXT). Clicking on this displays the full comment in a new form (is too large for listbox).

frmReport

Private Sub lstDocs_Click()
    Dim strSQL3 As String
 
    strSQL1 = "SELECT PAGE, DATE, NAME, TEXT FROM tblDATA WHERE NUM = '" & Me!lstResults & "'
    Me!lstNotes.RowSource = strSQL1
End Sub

Private Sub lstNotes_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmNote"
End Sub

frmNote

Private Sub Form_Load()
Me.Caption = "Comment"
Me!txtNote = Forms![frmReport]![lstNotes]
End Sub

When you click on the row displayed, it populates the new form with the first value (in the above case, it would display page). Changing the first value in the sql string to TEXT will then display TEXT when the form is opened.