Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Base a datasheet form on an array 1
- Thread starter Blorf
- Start date
-
1
- #2
Sorry Chaz, I'm not familiar with the term, "Memory Array".
But, if it is basically, a one dimensional array, I'd say you can. But, my approach, would still be, an unbound form?
On the load event, of the form, I would simply assign each element, in the array, to the appropriate control on the form.
Private Sub Form_Load()
Dim x As Integer, vArray As Variant
vArray = Array("Zero", "Two", "Four", "Six", "Eight", "ten", "Twelve", "One", "Three", "Five", "Seven", "Nine", "Eleven", "Thirteen")
For x = 0 To (Me.Controls.Count / 2) - 1 'just a sample form, you may want to hardcode the number...
Me("Text" & x) = vArray(x)
Next x
End Sub
If you want to find, desired control, count, dynamically, ...
Dim ctl As Control
For each ctl in Me.Controls
If ctl.ControlType = acTextBox Then
...if necessary...If Isnumeric(Mid(ctl.Name,4,1) Then
iControlCount = iControlCount + 1
End If
Next
Hope this helps, good luck!
But, if it is basically, a one dimensional array, I'd say you can. But, my approach, would still be, an unbound form?
On the load event, of the form, I would simply assign each element, in the array, to the appropriate control on the form.
Private Sub Form_Load()
Dim x As Integer, vArray As Variant
vArray = Array("Zero", "Two", "Four", "Six", "Eight", "ten", "Twelve", "One", "Three", "Five", "Seven", "Nine", "Eleven", "Thirteen")
For x = 0 To (Me.Controls.Count / 2) - 1 'just a sample form, you may want to hardcode the number...
Me("Text" & x) = vArray(x)
Next x
End Sub
If you want to find, desired control, count, dynamically, ...
Dim ctl As Control
For each ctl in Me.Controls
If ctl.ControlType = acTextBox Then
...if necessary...If Isnumeric(Mid(ctl.Name,4,1) Then
iControlCount = iControlCount + 1
End If
Next
Hope this helps, good luck!
- Thread starter
- #3
Thank you for the reply.
My application is this. I have a search form, where a user can enter a part number. The part numbers are not ours, but belong to several suppliers, and my searchable records are currently in the 200,000 area.
Also, I am removing all delimiters from the part nubmers and the search criteria (since everyone uses a different format) so a user enters 111-04-3, it searches for 111043.
Ok, the tricky part. The search is a form, with a datasheet subform, based on a query that uses an unbound control on the form for criteria. It runs kinda slow, especially on older machines. So, I was thinking instead of using criteria in a query, I would use a vb function that used an index, did a seek, load all the matching data into an array, and display the array.
Seemed like it would be faster.
And since there could be lots of results, just one record display won't work.
Sorry for the ling reply,
ChaZ
"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert
My application is this. I have a search form, where a user can enter a part number. The part numbers are not ours, but belong to several suppliers, and my searchable records are currently in the 200,000 area.
Also, I am removing all delimiters from the part nubmers and the search criteria (since everyone uses a different format) so a user enters 111-04-3, it searches for 111043.
Ok, the tricky part. The search is a form, with a datasheet subform, based on a query that uses an unbound control on the form for criteria. It runs kinda slow, especially on older machines. So, I was thinking instead of using criteria in a query, I would use a vb function that used an index, did a seek, load all the matching data into an array, and display the array.
Seemed like it would be faster.
And since there could be lots of results, just one record display won't work.
Sorry for the ling reply,
ChaZ
"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert
I believe what you're asking is feasible, not sure if faster .
(DAO) Seek , I believe, only finds one match. You may need to use (DAO) FindFirst & Find Next, if you go that route.
If you're going to open a recordset anyhow, to use Seek, why not filter the recordset in the first place, with your SQL (during open), loop & populate array.
So, if it's for display purposes, Then maybe an array, will give you more versitality. Get Rows Method?
If many records may be returned, you could use a Continous Form or a ListBox?
Chaz, is it populating the array you need help with, or advice, on how to improve on performance & viewing the results?
(DAO) Seek , I believe, only finds one match. You may need to use (DAO) FindFirst & Find Next, if you go that route.
If you're going to open a recordset anyhow, to use Seek, why not filter the recordset in the first place, with your SQL (during open), loop & populate array.
So, if it's for display purposes, Then maybe an array, will give you more versitality. Get Rows Method?
If many records may be returned, you could use a Continous Form or a ListBox?
Chaz, is it populating the array you need help with, or advice, on how to improve on performance & viewing the results?
- Thread starter
- #5
Definately advice on improving the performance. The array was kind of my thinking.
I was thinking that if I did a seek, and found the first match, it would be fast. I could use a .movenext and load an array while the matches continue.
May not be the best course.
I plan on moving the table to a SQL server database Monday to see if reading from there is faster also.
I appreciate any advice you can offer.
With respect to loading an array, I can do that, I was just wondering if you could trick a form into using an array as a table.
Thank you for the help.
"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert
I was thinking that if I did a seek, and found the first match, it would be fast. I could use a .movenext and load an array while the matches continue.
May not be the best course.
I plan on moving the table to a SQL server database Monday to see if reading from there is faster also.
I appreciate any advice you can offer.
With respect to loading an array, I can do that, I was just wondering if you could trick a form into using an array as a table.
Thank you for the help.
"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert
...again, seek & .movenext, won't work. The adjacent record, isn't necessarily in accord with the search criteria.
rec.FindFirst "txtCost > 1000"
Do While rec.NoMatch = False
x = x + 1
ReDim vArray(1 To x)
vArray(x) = rec!txtOrderNo
rec.findNext "txtCost > 1000"
Loop
...something like this...
again, since you open the recordset anyhow..
rec.Open ("tblOrders"),
why not
rec.Open ("SELECT * FROM tblOrders WHERE txtCost > 1000")
ReDim vArray(1 To rec.RecordCount)
For x = 1 To rec.RecordCount
vArray(x) = rec!txtOrderNo
Next
Not sure if relavent but, Roy Vidar made me aware, of using a recordset, for a recordsource on a form...
I'll post back, with the link.
...but, you do have a point. If you use an array, you will definately decrease the overhead of either opening a recordset each time, or processing(excuse the terminology), a SQL statement. Provided, you don't need to "Refresh" the array, every time?
Otherwise "I", don't see the expediancy, in populating an array, through a recordset, each time.
As far as viewing, I once created 3 adjacent listboxes, made to look like one. I divided the UBound(), of the array by 3. When the first 3rd, of the array, was reached, the 2nd list box began populating, etc...
Made for a clean, evenly dispersed, listbox, that showed a lot of data, at once.
...not sure Blorf, if this is helpful at all?
Don't forget about the DAO.GetRows Method...
rec.FindFirst "txtCost > 1000"
Do While rec.NoMatch = False
x = x + 1
ReDim vArray(1 To x)
vArray(x) = rec!txtOrderNo
rec.findNext "txtCost > 1000"
Loop
...something like this...
again, since you open the recordset anyhow..
rec.Open ("tblOrders"),
why not
rec.Open ("SELECT * FROM tblOrders WHERE txtCost > 1000")
ReDim vArray(1 To rec.RecordCount)
For x = 1 To rec.RecordCount
vArray(x) = rec!txtOrderNo
Next
Not sure if relavent but, Roy Vidar made me aware, of using a recordset, for a recordsource on a form...
I'll post back, with the link.
...but, you do have a point. If you use an array, you will definately decrease the overhead of either opening a recordset each time, or processing(excuse the terminology), a SQL statement. Provided, you don't need to "Refresh" the array, every time?
Otherwise "I", don't see the expediancy, in populating an array, through a recordset, each time.
As far as viewing, I once created 3 adjacent listboxes, made to look like one. I divided the UBound(), of the array by 3. When the first 3rd, of the array, was reached, the 2nd list box began populating, etc...
Made for a clean, evenly dispersed, listbox, that showed a lot of data, at once.
...not sure Blorf, if this is helpful at all?
Don't forget about the DAO.GetRows Method...
Here's the link,
Good Luck, either way!
Good Luck, either way!
- Thread starter
- #8
I thank you very much. Very helpful.
Let me ask though. If I create an index, and open the recordset, set the order to the index, then do a seek or a find or what not, why would the adjacant records not be what I am after?
Again, Thank you very much!
ChaZ
"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert
Let me ask though. If I create an index, and open the recordset, set the order to the index, then do a seek or a find or what not, why would the adjacant records not be what I am after?
Again, Thank you very much!
ChaZ
"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert
Just for the record, Only Seek requires, the criterion field, to be indexed.
Sorry Chaz, maybe you're right, If recordset is sorted by search field, the desired records, most likely will be adjacent. But you may have trouble, defining the boundaries?Because you won't use the rec.NoMatch then, maybe something like...
rec.Index = "chkPaid"
rec.Seek "=", True
Do Until rec.chkPaid = False
x = x + 1
ReDim Preserve vArray(1 To x)
vArray(x) = rec!txtAmount
rec.MoveNext
Loop
But what if your criteria, is like...
rec.FindFirst "txtCountry Like 'T*' Or txtCountry Like 'W*'"
...now, they won't be adjacent, no matter how you sort, (I just had to think of something, (it took me a while) LOL).
But this may not apply to you. As you see, it would just depend on your criterions.
Another error on my part, I noticed...
Do Until rec.NoMatch = False
Thank-you for the star, & good luck!
Sorry Chaz, maybe you're right, If recordset is sorted by search field, the desired records, most likely will be adjacent. But you may have trouble, defining the boundaries?Because you won't use the rec.NoMatch then, maybe something like...
rec.Index = "chkPaid"
rec.Seek "=", True
Do Until rec.chkPaid = False
x = x + 1
ReDim Preserve vArray(1 To x)
vArray(x) = rec!txtAmount
rec.MoveNext
Loop
But what if your criteria, is like...
rec.FindFirst "txtCountry Like 'T*' Or txtCountry Like 'W*'"
...now, they won't be adjacent, no matter how you sort, (I just had to think of something, (it took me a while) LOL).
But this may not apply to you. As you see, it would just depend on your criterions.
Another error on my part, I noticed...
Do Until rec.NoMatch = False
Thank-you for the star, & good luck!
- Thread starter
- #10
TheRealDeal
Technical User
Has anyone been able to create a Datasheet form on an array?
- Status
Similar threads
- Locked
- Helpful tip
- Locked
- Question
- Locked
- Question