From listbox to data sheet form... rowsource to recordsource

[morglum007]

Okay, copy that, thank you so much.

Tomorrow I ll give a try to this and will reply to this thread.

Thank you so much for your kindness, and have a nice day/week.

Best regards

Morg

 

[morglum007]

Re: From listbox to data sheet form... rowsource to recordsource (solved)

Hi there, finally I got it.

Thats how I did.

1.- Used the first approach Dcrake told to me, I mean, creating one data sheet form for each table, with same name as table.

Once there, inserted code in EVERY data sheet form on load event this way:

Option Compare Database
Private Sub Form_Load()
If StrFormSQL <> "" Then
   Me.RecordSource = StrFormSQL
Else
   MsgBox "Cannot find any records to display or criteria incorrect.", vbExclamation, "View Details"
   DoCmd.Close
End If
End Sub

In the search form, with three cascading comboboxes; "elementscb" for tables search, "fieldcb" for fields in tables and "finalchoosecb" for parameter search. There is also a command button in order to fire corresponding data sheet form and one multiple-column listbox named "searchlist"
This is the vba code in this form:

Private Sub elementscb_AfterUpdate()
Me![fieldcb].RowSource = Me![elementscb]
columnnumber = CurrentDb.TableDefs(elementscb).fields.Count
columntb.Value = columnnumber
TempVars("columnnumber").Value = columnnumber
If columnnumber > 6 Then
parametersbt.visible = True
Else
parametersbt.visible = False
End If
If columnnumber <= 20 Then
Searchlist.ColumnCount = columnnumber
Searchlist.ColumnWidths = 900
Else
Searchlist.ColumnCount = 10
Searchlist.ColumnWidths = 900
End If
Me.Searchlist.ColumnHeads = True
Me.Searchlist.RowSource = "SELECT " & elementscb & ".* FROM [" & elementscb & "]"
TempVars("elementscb").Value = elementscb.Text
If elementscb = "Users" Then
Searchlist.visible = False
MsgBox "Sorry, this is a restricted area!"
Else
Searchlist.visible = True
End If
End Sub
Private Sub fieldcb_AfterUpdate()
Me.finalchoosecb.RowSource = "SELECT DISTINCT [" & fieldcb & "] FROM [" & elementscb & "]"
TempVars("fieldcb").Value = fieldcb.Text
End Sub
Private Sub finalchoosecb_AfterUpdate()
Me.Searchlist.RowSource = "SELECT " & elementscb & ".* FROM [" & elementscb & "] WHERE [" & fieldcb & "] = finalchoosecb.Text"
TempVars("finalchoosecb").Value = finalchoosecb.Text
StrFormSQL = "SELECT * FROM [" & elementscb & "] WHERE [" & fieldcb & "] ='" & [finalchoosecb].Text & "'"
End Sub
Private Sub Form_Load()
parametersbt.visible = False
Searchlist.ColumnCount = 8
End Sub
Private Sub parametersbt_Click()
DoCmd.OpenForm ("" & elementscb & ""), acFormDS
End Sub

Code tips:
There is a "users" table where lies login and password for data base, so I force not to show such table in search.
There is also a column initialize in listbox (searchlist) and command button is disabled if results got less than 6 columns (in order to show just in listbox).

Thank you so much for your help Dcrake and hope this could help others.

Best regards

TIP: Rowsource from combobox have a DIFFERENT statement from recordsource in data sheet, so I have got to change public variable the way appears in code. Forgot to mention to create a module with bypass variable:

Option Compare Database
Public StrFormSQL As String