Having problems with Repaint NOT WORKING

[mrdod]

OK this is about my 200th post in the last two days so I'm about ready to jump off a cliff. I'm pulling data from a Lotus Notes database(Domino Server). I have some dummy code that imitates exactly what I'm trying to do at work. The problem is the progress bar doesn't update and the label doesn't update when I do the repaint. Sometimes the program locks up and I have to close Access but it has worked. Am I trying to do too much ? Is the program too busy ? Here is the code I just wrote to imitate what I had at work.

Private Sub cmdGoingCrazy_Click()
'*****Variables**********************************
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strConn As String
Dim lngnumrecords As Long
Dim i As Integer
Dim dblpercdone As Double
Dim n As Long
Dim r As Long

srtconn = "MyODBCConnection" 'This is my ODBC connection for a Lotus Notes DB

strSQL '=This is my SQL statement retrieving around 200 to 1500 records x 32 fields" _
depedinging on what choices the user selects"

cnt.Open strConn
DoCmd.OpenForm "FrmProgress" 'Progress Bar resides here
Form_FrmProgress.lblstatus = "Connecting to Lotus Notes DB..."
Form_FrmProgress.Repaint
Set rst = cnt.Execute(strSQL)

'Now I want to get how many records are being Queried.
rst.MoveFirst
Do Until rst.EOF
dummyvar = rst(0)
r = r + 1
rst.MoveNext
Loop

Form_FrmProgress.lblstatus = r & " records have been retrieved and will be loaded to Table X."
Form_FrmProgress.Repaint

rst.MoveFirst
Do While Not rst.EOF
i = 0
variable1 = rst(i).Value
variable2 = rst(i + 1).Value
'Etc... I have a total of 32 variables.

'Now I want to put into the table
strSQL = "INSERT INTO tblxxx([field],[field])"
strSQL = strSQL & " VALUES('" & variable1 & "','" & variable2 & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
n = n + 1 'Counts how many records have been sent to the table.
dblpercdone = n / r * 100
Form_FrmProgress.progressbar.Value = dblpercdone
Form_FrmProgress.Repaint
rst.MoveNext
Loop
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
Form_FrmProgress.lblstatus = "Process is complete."
Form_FrmProgress.Repaint
DoCmd.Close acForm, "FrmProgress"

End Sub

Please Bash Away I just want to know the best way to do this. Thanks in advance!!

 

[PHV]

You may use the DoEvents function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrdod]

Thanks again PH you've helped me once again!!

I'm not all that familiar with the DoEvents function but I put it on the line above every time I needed something done. Is that correct? Would it work better if I had all these different items in their own function?

DoEvents
Form_frmrefresh.lblstatus.Caption = "Loading " & r & " files to Table."
DoEvents
Form_frmmain.Repaint

Thanks again!!!

 

[Crowley16]

erm, would it not be easier to link the view from lotus domino notes as a linked table, and then just use a select into statement?

that should run much quicker than what you're doing...

p.s. it can be a bit fiddly setting up the dsn, but if you stick with it, it will work...

--------------------
Procrastinate Now!

 

[EckyThump]

I had a similar problem of having a progress bar control not update and actually it still doesn,t update first time if I have made changes to the table or query structures, but after the first time and having saved the database everything seems ok.
I also had turned Echo on or off (DoCmd.Echo True) for some reason??? that seemed to make a difference.
Long time ago and things are a bit grey now, but something to investigate perhaps.

 

[mrdod]

I'm not sure it would be easier for me Crowley as I have no idea how to go about doing it but it does sound like it would be a nice thing to have. On a scale of 1 - 10 I don't think I've hit 1 with my VBA knowledge. Especially compared to people on this forum. I've never had any training, only what I see here so please talk down to me. LOL pictures help!!

Thanks for the suggestion I'll try and investigate.

 

[mrdod]

I have tried to Link the table but I keep getting an alarm saying the file is too large. Is there a way to create a linked table based on an SQL statement?