Change datasheet source dynamically
Someone asked a question like mine but to be honest I don't understand how the solution worked:
https://www.experts-exchange.com/questions/21605071/Changing-Fields-on-a-subform-dynamically-with-VBA.html?query=dynamic+query+source&topics=39
I want to create a 'List Maintenance' form. It will have an option group in which the toggle buttons' tag will contain the table name that will be the source of the subform. As with the other post, I don't want to have saved queries and I also want to hide the first column (the id field). Also, the number of fields varies from table to table.
I tried the following code:
'*************************
strSQL = "": strDelim = ""
strSource = Me("btn" & optMaintItem).Tag
Set rst = CurrentDb.OpenRecordset(st
With rst
For intLoop = 1 To (.Fields.Count - 1)
strSQL = strSQL & strDelim & .Fields(intLoop).Name
strDelim = ", "
Next intLoop
.Close
End With
Set rst = Nothing
Set qdf = CurrentDb.QueryDefs("qryLi
With qdf
.SQL = "Select " & strSQL & " From " & strSource
.Close
End With
Set qdf = Nothing
Me.subHolder.Form.Requery
'*************************
This seems to build the query ok when I switch between toggle buttons, but the subform never seems to requery. I have to close the form and re-open to see the changed sql source.
Also, I am worried about a multi-user environment as each user will be using the front end on the server, not on their desktops. What will happen if one user is trying to maintain 'Customer Types' and other user switches to maintain 'Phone Types'?
Is there an easier way to do this? (ps. The subform is in datasheet view, and I could not get .ColumnHidden to work on the ID column)
https://www.experts-exchange.com/questions/21605071/Changing-Fields-on-a-subform-dynamically-with-VBA.html?query=dynamic+query+source&topics=39
I want to create a 'List Maintenance' form. It will have an option group in which the toggle buttons' tag will contain the table name that will be the source of the subform. As with the other post, I don't want to have saved queries and I also want to hide the first column (the id field). Also, the number of fields varies from table to table.
I tried the following code:
'*************************
strSQL = "": strDelim = ""
strSource = Me("btn" & optMaintItem).Tag
Set rst = CurrentDb.OpenRecordset(st
With rst
For intLoop = 1 To (.Fields.Count - 1)
strSQL = strSQL & strDelim & .Fields(intLoop).Name
strDelim = ", "
Next intLoop
.Close
End With
Set rst = Nothing
Set qdf = CurrentDb.QueryDefs("qryLi
With qdf
.SQL = "Select " & strSQL & " From " & strSource
.Close
End With
Set qdf = Nothing
Me.subHolder.Form.Requery
'*************************
This seems to build the query ok when I switch between toggle buttons, but the subform never seems to requery. I have to close the form and re-open to see the changed sql source.
Also, I am worried about a multi-user environment as each user will be using the front end on the server, not on their desktops. What will happen if one user is trying to maintain 'Customer Types' and other user switches to maintain 'Phone Types'?
Is there an easier way to do this? (ps. The subform is in datasheet view, and I could not get .ColumnHidden to work on the ID column)
1. Subform not showing correct data unless closed and re-opened.
2. What are the ramifications of using this code in a multi-user environment.
3. Is there and easier way.
4. ColumnHidden does not work.
so I guess there are 4 questions and not just one :-)
1. Did rocki's suggestion straighten this out or are you still having peroblems?
2. Multi-User environments are completely different creatures ... you split the data into a seperate database field and then link the tables back into the front end (this is the database fiel that has all the quersries, forms, reports and code) Then you put the FE (front end) onto all of your users desktops and put the data on the network. With this setup the code (once working correctly) that you have now will be fine.
3. Yes ... I know you don't want to hear it but having seperate queries / forms for your maintenance tables is actually much easier, cleaner and faster.
4. Be careful with hiding columns, users can change the position of columns in datasheetview so it may not always be column(0). You would have to re-set your column widths each time you re-set the recordsource. Can you post the code you are using for hiding the column so we can help you fix it?
Steve
2. What are the ramifications of using this code in a multi-user environment.
3. Is there and easier way.
4. ColumnHidden does not work.
so I guess there are 4 questions and not just one :-)
1. Did rocki's suggestion straighten this out or are you still having peroblems?
2. Multi-User environments are completely different creatures ... you split the data into a seperate database field and then link the tables back into the front end (this is the database fiel that has all the quersries, forms, reports and code) Then you put the FE (front end) onto all of your users desktops and put the data on the network. With this setup the code (once working correctly) that you have now will be fine.
3. Yes ... I know you don't want to hear it but having seperate queries / forms for your maintenance tables is actually much easier, cleaner and faster.
4. Be careful with hiding columns, users can change the position of columns in datasheetview so it may not always be column(0). You would have to re-set your column widths each time you re-set the recordsource. Can you post the code you are using for hiding the column so we can help you fix it?
Steve
Hello UniqueData
It strikes me that you are building a query looking like this:
SELECT <field list> FROM <source>
You could just use
SELECT * FROM <source>
or even
TABLE <source>
As your SQL string, saving you the trouble of the recordset and the list of fields...
(°v°)
It strikes me that you are building a query looking like this:
SELECT <field list> FROM <source>
You could just use
SELECT * FROM <source>
or even
TABLE <source>
As your SQL string, saving you the trouble of the recordset and the list of fields...
(°v°)
Ah, sorry, missed the 1 in the control loop; you skip the first field...
(°v°)
(°v°)
ASKER
4 questions..my bad...it was a looong day when I posted last night
1. I will have to try out rocki's suggestion tonight (another long day...)
2. I see there are several posts about scripts to update the front end. I will check into that so each will have their own copy
3. I am using this project to try and make things as dynamic as possible for implimentation in other databases. I figured if I used the buttons tag there would be less things to create when new list item tables are created.
4. When I get back to the project tonight I will post this as a seperate question and put the link here (the whole user moving the columns is very interesting and I think I know a better way to ask the question)
5. Is it Friday yet??!!!???
1. I will have to try out rocki's suggestion tonight (another long day...)
2. I see there are several posts about scripts to update the front end. I will check into that so each will have their own copy
3. I am using this project to try and make things as dynamic as possible for implimentation in other databases. I figured if I used the buttons tag there would be less things to create when new list item tables are created.
4. When I get back to the project tonight I will post this as a seperate question and put the link here (the whole user moving the columns is very interesting and I think I know a better way to ask the question)
5. Is it Friday yet??!!!???
5. YES !!!!! :-)
3. I have done exactly what you are working on in the past and what I found was that I ended up embedding too much information in the Tag and/or configuration tables to drive these correctly. Let me ask this Q .... assuming that a simple lookup list is comprised of unique values, do you just leave Access to throw up on duplicate entries that are typically meaningless to users? If not then you need to start doing lots of tricky stuff so you know what message for which field for what goes wrong in which app? Using a generic form also limits you to not using input masks (I never do anyway but it is something to consider) or any kind of formatting of the data at all, you can't even assume what should be left, right ior center justified. All that being said ... I'll help as best I can :-)
Steve
3. I have done exactly what you are working on in the past and what I found was that I ended up embedding too much information in the Tag and/or configuration tables to drive these correctly. Let me ask this Q .... assuming that a simple lookup list is comprised of unique values, do you just leave Access to throw up on duplicate entries that are typically meaningless to users? If not then you need to start doing lots of tricky stuff so you know what message for which field for what goes wrong in which app? Using a generic form also limits you to not using input masks (I never do anyway but it is something to consider) or any kind of formatting of the data at all, you can't even assume what should be left, right ior center justified. All that being said ... I'll help as best I can :-)
Steve
ok ... here is the code ...
things of interest ...
main form has an option group named: grpTable
main form has toggle buttons named: toggle1, toggle2, etc.
subform has textboxes named: txtFiled1, txtFiled2, etc.
Private Sub grpTable_AfterUpdate()
Call FixupAdmin(Me.Controls("to
End Sub
Private Sub FixupAdmin(TableName As String)
Dim fld As DAO.Field
Dim intLoop As Integer
Dim intCount As Integer
Dim frm As Access.Form
Dim txt As Access.TextBox
Set frm = Me.childAdmin.Form
frm.RecordSource = "SELECT * FROM " & TableName
intCount = frm.Recordset.Fields.Count
For intLoop = 1 To intCount
Set txt = frm.Controls("txtField" & intLoop)
txt.ControlSource = "[" & frm.Recordset.Fields(intLo
txt.Visible = True
txt.ColumnOrder = intLoop - 1
txt.ColumnWidth = -1
Next
'cleanup textboxes not used
For intLoop = intCount + 1 To 5
Set txt = frm.Controls("txtField" & intLoop)
txt.ControlSource = vbNullString
txt.ColumnWidth = 0
Next
End Sub
things of interest ...
main form has an option group named: grpTable
main form has toggle buttons named: toggle1, toggle2, etc.
subform has textboxes named: txtFiled1, txtFiled2, etc.
Private Sub grpTable_AfterUpdate()
Call FixupAdmin(Me.Controls("to
End Sub
Private Sub FixupAdmin(TableName As String)
Dim fld As DAO.Field
Dim intLoop As Integer
Dim intCount As Integer
Dim frm As Access.Form
Dim txt As Access.TextBox
Set frm = Me.childAdmin.Form
frm.RecordSource = "SELECT * FROM " & TableName
intCount = frm.Recordset.Fields.Count
For intLoop = 1 To intCount
Set txt = frm.Controls("txtField" & intLoop)
txt.ControlSource = "[" & frm.Recordset.Fields(intLo
txt.Visible = True
txt.ColumnOrder = intLoop - 1
txt.ColumnWidth = -1
Next
'cleanup textboxes not used
For intLoop = intCount + 1 To 5
Set txt = frm.Controls("txtField" & intLoop)
txt.ControlSource = vbNullString
txt.ColumnWidth = 0
Next
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to see it in action (A2003) I uploaded a sample mdb.
https://filedb.experts-exchange.com/incoming/ee-stuff/2048-TEST.zip
Steve
https://filedb.experts-exchange.com/incoming/ee-stuff/2048-TEST.zip
Steve
ASKER
Steve,
Thanks for the sample. It worked perfectly!!!
Thanks for the sample. It worked perfectly!!!
ASKER
Just for other's reference...
The line:
txt.ColumnWidth = (intLoop <> 1)
Should have been
txt.ColumnHidden = (intLoop = 1)
The line:
txt.ColumnWidth = (intLoop <> 1)
Should have been
txt.ColumnHidden = (intLoop = 1)
Me.subHolder.Form.RecordSo