Access VBA search RecNr and fill textboxes slow

[Priyan]

Dear experts

I’m bit stuck here. hours and hours of research in web didn’t help me.
The code I wrote to fill text boxes in an access form works but its very slow. It’s really nice if someone can advice me how to optimize the code.

The frmWochenPaln_Main is a bound, continuous form to the qryLayOut. In the form body there are 18 textboxes, those control source are set to the 18 fields of the query.
Screenshot: db.tt/IJmZQJWs

The 18 text boxes in the forms detail area are laid out like this:
txbA1P1RecNr txbA2P1RecNr txbA3P1RecNr txbA4P1RecNr txbA5P1RecNr txbA6P1RecNr
txbA1P2RecNr txbA2P2RecNr txbA3P2RecNr txbA4P2RecNr txbA5P2RecNr txbA6P2RecNr
txbA1P3RecNr txbA2P3RecNr txbA3P3RecNr txbA4P3RecNr txbA5P3RecNr txbA6P3RecNr

In addition to the above 18 textboxes with record numbers, there are 18 blocks of 12 textboxes. Wichich contains Material number, Material description and so on. In these textboxes the control source is a function.
Screenshot db.tt/nKz5Gguk


What I’m trying to achieve is on the opening of the form or on refresh the form code as to go through all the 18 RecNr textboxes till the end of the records (it’s always 6 records) on the continues form. If value found, search this value in the table tblProdPlan_Woche and fill the corresponding textboxes with rest of the field values found for the record in table tblProdPlan.

Screenshot db.tt/VqZ5DFKH

Thank you very much in advance for your comments.

Priyan


The Code:
My Code looks like this:
Public MatBez_x As String
Public db As DAO.Database
Public rs As DAO.Recordset

Public strTextboxName As String
Public pubMatnr, pubMatbez, pubAnzMisch, pubAufGroesZUN, pubBeutelzahl, pubBeutelFormat, pubEinAufMeng, pubInfo1, pubInfo2, pubKartFormat, pubMRDRMisch, pubStartZeit As String

Public Function ConnectionOpen()
Set db = CurrentDb
Set rs = db.OpenRecordset("qryWocheDetailBereich")

End Function

Public Function ConnectionClose()
Set db = Nothing
Set rs = Nothing
End Function


Public Function MatNr(CtlGID As String) As String

ConnectionOpen

' Formname und textfeld
Dim frm As Form
Dim txb As TextBox

strTextboxName = "txb" & CtlGID & "RecordNr"
Set frm = Forms("frmWochenPlan_Main")
Set txb = frm.Controls(strTextboxName)

' Check if there is a record number available in the RecordNr txb
If txb.Value <> "" Then

rs.FindFirst "ProdPlanW_ID = " & txb.Value
MatNr = Nz(rs.Fields("Mat_MRDR_Artikel"), "")

' füllt die public Variabeln ab

pubMatnr = Nz(rs.Fields("Mat_MRDR_Artikel"), "")
pubMatbez = Nz(rs.Fields("Mat_Bez_Kurz"), "")
pubAnzMisch = Nz(rs.Fields("Anzahl_Mischung"), "")
pubAufGroesZUN = Nz(rs.Fields("Auftragsgroesse_ZUN"), "")
pubBeutelzahl = Nz(rs.Fields("Mat_BeutelProZUN"), "")
pubBeutelFormat = Nz(rs.Fields("Mat_BeutelFormat"), "")
pubEinAufMeng = ""
pubInfo1 = Nz(rs.Fields("Mat_Infofled1"), "")
pubInfo2 = Nz(rs.Fields("Infofeld2"), "")
pubKartFormat = Nz(rs.Fields("Mat_Kartonformat"), "")
pubMRDRMisch = Nz(rs.Fields("Mat_MRDR_Mischung_Komp"), "")
pubStartZeit = Nz(rs.Fields("Start_Zeit") & rs.Fields("Start_Tag"), "")

Else

pubMatnr = ""
pubMatbez = ""
pubAnzMisch = ""
pubAufGroesZUN = ""
pubBeutelzahl = ""
pubBeutelFormat = ""
pubEinAufMeng = ""
pubInfo1 = ""
pubInfo2 = ""
pubKartFormat = ""
pubMRDRMisch = ""
pubStartZeit = ""

End If
ConnectionClose
End Function

Public Function MatBez(CtlGID As String) As String
MatBez = pubMatbez
End Function

Public Function EinAufMeng(CtlGID As String) As String
EinAufMeng = pubEinAufMeng
End Function


Public Function Beutelzahl(CtlGID As String) As String
Beutelzahl = pubBeutelzahl
End Function


Public Function StartZeit(CtlGID As String) As String
StartZeit = pubStartZeit
End Function


Public Function Info1(CtlGID As String) As String
Info1 = pubInfo1
End Function


Public Function Info2(CtlGID As String) As String
Info2 = pubInfo2
End Function


Public Function BeutFormat(CtlGID As String) As String
BeutFormat = pubBeutFormat
End Function

Public Function AufGroesZUN(CtlGID As String) As String
AufGroesZUN = pubAufGroesZUN
End Function

Public Function MRDRMisch(CtlGID As String) As String
MRDRMisch = pubMRDRMisch
End Function

Public Function AnzMisch(CtlGID As String) As String
AnzMisch = pubAnzMisch
End Function

Public Function KartFormat(CtlGID As String) As String
KartFormat = pubKartFormat
End Function

 

[Yonnel]

Hello,

If the form display is too slow, it is probably because your source query (qryWocheDetailBereich) is too big or complex.

=> Is it using some data stored on the network?
If yes, the best is probably to first copy the data locally and then base the query on the local data

=> Is your query making complex calculations?
If yes, you can transform the query into a make table query and then base the form on the output of this query

-----
Lionel Garnier
http://www.gylsolutions.fr - IT consulting, VBA, Access trainings, Excel trainings, PowerPoint trainings

 

[Priyan]

Hi Yonnel
Thank you for the reply.
Actually the query “qryWocheDetailBereich“ is not complex at all, I mean no calculations. I use it to filter records belonging to the period form the table “tblProdPlan_Woche”. The table is in the same db not remote.

Each textboxes in the continuous form has a function in the control source.
For example:
=MatNr("A1P1")
=MatBez("A1P1")
=EinAufMeng("A1P1")
=Beutelzahl("A1P1")
This is because I couldn’t find a better way to populate the text boxes in continuous form.

For example, each time the form opens the text box function MatNr("A1P1") check if there is a value in txb “txbA1P1RecordNr” if yes it search this value(record number) in the query “qryWocheDetailBereich” and display the material number found in the record. This routine repeats through 1296 textboxes in the form.

I use the following code to search the records:
strSQL = "SELECT * FROM qryWocheDetailBereich WHERE ProdPlanW_ID=" & txb.Value
Set rs = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)

Is there a better way to populate text boxes in continous form?
Is there a efficient search option than SELECT?

Thanks again
Priyanga

 

[spikepl]

You could do what you are doing more efficiently. However, the main issue is what is the intent of all this?

Is this a display of data or is the form intended to both display and edit data? A form with 1000+ fields? That does not sound right.

Attach pictures as per the sticky at the very top of this forum.

 

[Yonnel]

Hi,

I don't catch exactly what you want to do..! But I think there is an easier way to do it

I agree with spikepl, can you give us some more info?
You have 1296 text boxes for each record (horizontally) or 1296 records (vertically)?

A basic principle is that your population needs to be defined in the source of the form, and not in the form itself. So you need to apply the functions in the query when possible.

-----
Lionel Garnier
http://www.gylsolutions.fr - IT consulting, VBA, Access trainings, Excel trainings, PowerPoint trainings

 

[Priyan]

Hi guys
Thank you for your time
I know I’m doing something wrong here, but I don’t know what.

Form in design view: ://db.tt/hWFtmHKB
Form in form view: ://db.tt/41qu6Tue
copy of DB in ZIP: ://db.tt/XXy8gqP7


Since I don’t have 10 posts yet I cant post pictures. I placed my pictures and copy of db in ZIP format in my Dropbox. Please place http in front of above links then you can see the pictures.
Please let me know if above links doesn’t work

regards

Priyan

 

[Priyan]

Hi everyone

I assume you are able to see the pictures.

What I’m trying to do here is like MS outlook like grid. The grid shows 6 days per week vertically and the machines are on top. Per day for a machine you have three place holders consists of 12 textboxes.

12 textboxes x 3 Positions x 6 Machines = 216 x 6 rows on continuous form = 1296 textboxes

The form is bound to the query “qryLayOut”. It holds the record numbers belong to the lay out. There are invisible textboxes call “txbA1P1RecordNr” those control sources are link to the query.

Naming conventions A = Six machines; P = Position of a machine
txbA1P1RecordNr = machine one from left and position one
txbA1P2RecordNr = machine one from left and position two
txbA2P3RecordNr = machine two form left and position three


All the other textboxes are equally named. txbA1P1MatNr, txbA1P2MatDescription and so on. And these are unbound. And in the control source of each txb has a function. Why function?
I couldn’t find any other way to populate text boxes on a continues form. Every time the textbox “txbA#P#RecordNr” changes code searches the record number in the “qryWocheDetailBereich” and populate the corresponding 12 text boxes.

Yet to program:
The date and year selection of the form.
Double click on any where on a position (within 12 textboxes block) should open a pop up. So that user can create a new record, edit or delete.

The biggest problem is the population the detailed area of the form is too slow.

Regards
Priyan

 

[gemma-the-husky]

populating a vast number of text boxes is not the way to go.

you need to try and construct a query that collects all the information, and bind your form to it. it's sometimes hard to get into a database thoughtset - but you need to do so.

sometimes you have to populate text boxes manually - but that is maybe for something like a dashboard, where the data is just not directly related.

 

[Yonnel]

Whaou! This is indeed a lot of text boxes!!!

And for me the form is not too long to load if you consider all the operations that are done...!

For the rest I fully agree with gemma-the-husky.

The approach I would suggest is:

1. Create one table containing all the information needed with one record per day (in your case, there will be 217 columns!)
2. Bind the fields in the form directly to the fields of the table
3. When changing the value of "cbxBereich" (and other fields in the future), run a query to update the content of this table based on your selection
   => I will still take quite some time to calculate but this will give you the possibility to display a progress message for example...

Then I have another comment: There is really too much information of that screen which makes it difficult to read! Can you display only a part of it? The rest would be displayed when clicking on a specific button for example.

 

[Priyan]

Hi husky

I tried to build a query too.

Actually on my qryLayout I have all the record numbers that I need to place on the form. I only need to link the qryLayout with tblProdPlan_Woche.

But the thing is the qryLayout has 18 fields with the record numbers. Only way I know to get the record details is to make 18 copies of tblProdPlan_Woche and link each copy to 18 fields of the qry with the key field record number. And this makes the loading of the query really slow.

Is there a better way to achieve that?

Regards

Priyan

 

[Priyan]

The approach I would suggest is:

1. Create one table containing all the information needed with one record per day (in your case, there will be 217 columns!)
2. Bind the fields in the form directly to the fields of the table
3. When changing the value of "cbxBereich" (and other fields in the future), run a query to update the content of this table based on your selection
   => I will still take quite some time to calculate but this will give you the possibility to display a progress message for example...

Great idea!
But how can i fill a table with all these information efficiently?
Do you mean with record set?
How to handle it in multiuser environment?

Then I have another comment: There is really too much information of that screen which makes it difficult to read! Can you display only a part of it? The rest would be displayed when clicking on a specific button for example.

I thought of expanding the width of the body. And then user can do a vertical scroll.