Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Loading a form Programmatically

Posted on 2005-04-28
30
Medium Priority
?
232 Views
Last Modified: 2010-05-02
This is probably stupid simple, but I need to load a form programmatically. I'm having a problem refreshing data in some controlls that are linked to a database, it will do it...but only on the second try. I've tried everything I know of except for unloading and reloading that form. Can anyone help??
0
Comment
Question by:tim_a_cook
  • 15
  • 13
  • +1
30 Comments
 
LVL 12

Expert Comment

by:Preece
ID: 13889965
I've always avoided using data-bound controls.  I prefer the good old-fashioned method of getting a recordset and manually populating controls.  

Anyways, which controls are you having trouble with and how are you refreshing them?  Are you not updating the rs after adding a rec, for example?

Preece
0
 
LVL 8

Expert Comment

by:hpdvs2
ID: 13890027
If you know how to work with SQL then yes.  

Dim Conn as new adodb.connection
dim RS as new adodb.Recordset

Conn.Open "DSN or ODBC Name goes here"
RS.Open "SELECT * FROM SomeTable WHERE ID = " & ID, Conn

if not RS.EOF then
  Text1.text = RS("FieldName")
  txtName.Text = RS("Name")
  txtEmail.Text = RS("Email")
end if

RS.Close


You may need to add ADODB as a reference   (done by menu, View=>References, and then check off ADODB Data Object 2.X where X is the highest number it shows you.

HAve fun
0
 

Author Comment

by:tim_a_cook
ID: 13890048
Actually I am using the method you are a fan of...manually populating controls using a recordset. At any rate there are two list boxes in a control array. To refresh, I put the code that fills the list boxes in a module so evryone can see it. The update happens when the user clicks the "Done" button on the service form. Then, just before unloading the service form runs the code in the module that fills the listboxes.

Thanks,

-TIMMY
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:tim_a_cook
ID: 13890099
hpdvs2 -  actually I am doing something very simmilar...there is probably a much more efficient way of doing it, but this is what ive done (for the most part anyway):

'the name of my connection is dbCon
'there is also a TruckNumber string that i'm using...its basically an ID number

Dim RSA as New ADODB.Recordset
Dim RSB as New ADODB.Recordset

RSA.Open "SELECT EquipCode, FourHundred, EightHundred, TwelveHundred FROM ByNumber WHERE EquipCode = '" & TruckNumber & '"', dbCon

'then to update I do this, and this is also in a while loop loop goes after RSA.Open and before RSB.Open
RSB.Open "UPDATE ByNumber SET TwelveHundred = 2 WHERE EquipCode = '" & TruckNumber & "'", dbCon

thanks,

-TIMMY
0
 
LVL 12

Expert Comment

by:Preece
ID: 13890153
Just some simple guidelines, which you are probably using:

-  On form load, call procedure that fills list boxes.  
-  When user clicks done, update the db, then call the same procedure to fill the list boxes...

Are you saying that you fill the list boxes after clicking Done and before unloading the form?  So the Done button updates then immediatley unloads the form?  If I'm understanding you correctly, then maybe one button should update the db and "refresh" the listboxes, and another simply unloads the form...

It may help to see some code as well...

Preece
0
 

Author Comment

by:tim_a_cook
ID: 13890460
ok, I tried the two button thing and it gave me the same symptoms...have to do it twice before dissappearing from the listbox. But if I only do run through it once, then close the program and open the database...the value HAS changed. Here's the code for the "Done" Button:

Public Sub btnDone_Click()

Dim VehicleNumber As String
VehicleNumber = ServiceForm.txtTrk.Text

Dim RSA As ADODB.Recordset  'For Selecting the Record
Set RSA = new ADODB.Recordset

Dim RSB As ADODB.Recordset  'For Updating the Record
Set RSB = new ADODB.Recordset

RSA.Open "SELECT EquipCode, FourHundred, EightHundred, TwelveHundred FROM ByNumber WHERE EquipCode = '" & VehicleNumber & "'", Con, adOpenDynamic           'of course this is all one one line in the actual program

While Not RSA.EOF
     If RSA("FourHundred") = 1 Then
          RSB.Open "UPDATE ByNumber SET FourHundred = 2 WHERE EquipCode = '" & RSA("EquipCode") & "'", Con, adOpenDynamic                     'again on one line
     End If

     If RSA("EightHundred") = 1 Then
          RSB.Open "UPDATE ByNumber SET EightHundred = 2 WHERE EquipCode = '" & RSA("EquipCode") & "'", Con, adOpenDynamic
     End If

     If RSA("TwelveHundred") = 1 Then
          RSB.Open "UPDATE ByNumber SET EightHundred = 2 WHERE EquipCode = '" & RSA("EquipCode") & "'", Con, adOpenDynamic
     End If

     RSA.MoveNext

Wend

RefreshServiceForm   'refreshes the listboxes in the Service Form
Unload Me

End Sub


If you want to see the code for the refreshing let me know...and if there are any typos' its not actually in the code...i cant copy and paste because the computer i'm using for the program is not connected to the internet...yeah dont worry about that one...my workplace is...different (you can change that last word to whatever you want).

-TIMMY
0
 

Author Comment

by:tim_a_cook
ID: 13890504
guess its harder than i thought...the answer is now worth 500 points
0
 
LVL 12

Expert Comment

by:Preece
ID: 13890562
I think that maybe you should not use a recordset for an update query.  Take a look at this:

Option Explicit
Private cnnAdo As ADODB.Connection
Private rstAdo As ADODB.Recordset
Private cmdAdo As ADODB.Command

Public Sub gsnExecSql(sProc As String)
    On Error GoTo HandleError
   
    Set cmdAdo = New ADODB.Command
    Set cmdAdo.ActiveConnection = cnnAdo
    cmdAdo.CommandText = sProc
    cmdAdo.Execute  
   
    Exit Sub

HandleError:
    MsgBox "An error occured.  Error #: " & Err.Number & ", Description:  " & Err.Description
    Resume Next
End Sub

Try using the command object's execute method...

Preece
0
 

Author Comment

by:tim_a_cook
ID: 13890592
Here is the code that is filling the listboxes with data (refreshing):

Public Sub RefreshServiceForm()

' db connection is called myDbConn

ServiceForm.lstMixers(0).Clear
ServiceForm.lstMixers(1).Clear

Dim RSList1 As ADODB.Recordset
Set RSList1 = New ADODB.Recordset

RSList1.Open "SELECT EquipCode, TruckType, TwoHundred, FourHundred, EightHundred, TwelveHundred FROM ByNumber WHERE TruckType = 'MIXER'", myDbConn, adOpenDynamic  'For Selecting Mixers

While Not RSList1.EOF
If RSList1("TwoHundred") = 1 Then
     ServiceForm.lstMixers(0).AddItem(RSList1("EquipCode"))
End If

If RSList1("FourHundred") = 1 Then
     ServiceForm.lstMixers(0).AddItem(RSList1("EquipCode"))
End If

If RSList1("EightHundred") = 1 Then
     ServiceForm.lstMixers(0).AddItem(RSList1("EquipCode"))
End If

If RSList1("TwelveHundred") = 1 Then
     ServiceForm.lstMixers(0).AddItem(RSList1("EquipCode"))
End If

RSList1.MoveNext
Wend

'the same thing happens for the other list box called lstMixers(1)


-TIMMY
0
 

Author Comment

by:tim_a_cook
ID: 13890599
thanks preece, i'll give that a shot and let you know whether or not it worked
0
 

Author Comment

by:tim_a_cook
ID: 13890634
ok, i'm confused...i'm rather new at VB (if you couldent tell). What exactly is that command going to do?? I guess my problem is i'm not sure where to put it or what to do with it? could you explain the functionality of that a bit. Please??

Thanks Preece

-TIMMY
0
 
LVL 12

Expert Comment

by:Preece
ID: 13890674
Generally, I've used a recordset with the open method to get data (select statement) only.  But to update, delete, or insert a rec, I've used the execute method of the command object.  Look it up in VB help for detailed explanations, but this has worked well for me for years.  

So, you'll connect to the db using the connection object (cnnAdo), which you are probably doing, then use the command object (cmdAdo) as shown below:

Option Explicit
Private cnnAdo As ADODB.Connection
Private rstAdo As ADODB.Recordset
Private cmdAdo As ADODB.Command

Public Sub gsnExecSql(sProc As String)
    On Error GoTo HandleError
   
    Set cmdAdo = New ADODB.Command
    Set cmdAdo.ActiveConnection = cnnAdo
    cmdAdo.CommandText = sProc
    cmdAdo.Execute  
   
    Exit Sub

HandleError:
    MsgBox "An error occured.  Error #: " & Err.Number & ", Description:  " & Err.Description
    Resume Next
End Sub
0
 
LVL 12

Expert Comment

by:Preece
ID: 13890691
Also, have a look at the following ee question for more details on implementation:

http://www.experts-exchange.com/Programming/Q_21405277.html
0
 

Author Comment

by:tim_a_cook
ID: 13890861
I keep getting the error: Item cannot be found in the collection corresponding to the requested name or ordinal.

Then its pointing to the string deffinition for the command string. I'll get it figured out eventually...i'm only half way through my second quarter of programming in college. Havent got to Database Programming yet, but we've touched on some stuff (such as connections and recordsets)...I understand what the ADODB.Command is doing though.

At any rate I've got to go take care of some other things first, so i'll work on this later tonight and let you know if it worked. Thanks a lot Preece!

-TIMMY
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 13893412
>> Item cannot be found in the collection corresponding to the requested name or ordinal

I think you are trying to read from a field that you have not selected into your recordset.  Take, for example:

      oRS.Open "SELECT [a], [b], [c] FROM [table];", oConn

           a = oRS.Fields("a").Value
           b = oRS.Fields("b").Value
           c = oRS.Fields("c").Value
           d = oRS.Fields("d").Value     '<-- THIS WILL GENERATE THE ERROR, AS d WAS
                                                     '      NOT IN THE SELECT STATEMENT
      oRS.Close

HTH

J.
0
 

Author Comment

by:tim_a_cook
ID: 13896587
Using the above ADODB Command object I am now getting the error: Object variable or With block variable not set

And its pointing to: Set cmdOne.ActiveConnection = Con

any ideas?
0
 

Author Comment

by:tim_a_cook
ID: 13896735
ok, forget that last post...i'm an idiot. And with the other error I was getting...yeah helps if the recordset you are using is BEFORE the code that is using it...its an ID10T error. Anyway, I got the ADODB Command object working correctly and its still having the same syptoms...takes twice to get the listboxes to update correctly...could it have something to do with maybe another recordset elsewhere in the program locking the record?? I havent set adLock--- on anything so its default...any new ideas?? Thanks,

-TIMMY
0
 
LVL 12

Accepted Solution

by:
Preece earned 2000 total points
ID: 13896874
If I'm understanding your scenario correctly, I'll go back to my original suggestion:

-  Try using one sub to load a listbox.
-  On form load, call this sub.
-  When user is finished editing, they click a 'Save' or 'Update' button.
-  The code under that button should validate,  update the db, then make the same call to the sub that loads the listbox


Preece
0
 
LVL 12

Expert Comment

by:Preece
ID: 13896928
Another thing, it is recommended that you close and destroy your recordsets when finished:

RSList1.Close
Set RSList1 = Nothing
0
 
LVL 12

Expert Comment

by:Preece
ID: 13896952
One more thing, either use something like what's in the gsnExecSql I posted to update the table, or close the recordset(s) before refreshing....

0
 

Author Comment

by:tim_a_cook
ID: 13896985
haha, ok i dont know why i find this funny, but it does something simmilar. While watching the listboxes, when you press the update button it does nothing...press it a second time and you can watch the data that is supposed to dissapear...dissapear. I think the programming gods are playing a sick joke on me. Any ideas for that one?

-TIMMY
0
 
LVL 12

Expert Comment

by:Preece
ID: 13896986
er, the above posting should read:

Either use something like what's in the gsnExecSql I posted to update the table, or close and destroy the recordsets in btnDone_Click before calling RefreshServiceForm.  
0
 

Author Comment

by:tim_a_cook
ID: 13897003
yeah i went through and closed all other recordsets and connections in the program...dont think i missed anything.
0
 
LVL 12

Expert Comment

by:Preece
ID: 13897022
Well, it is interesting that you have the unload me right after refreshing in btbDone.  Just for fun, take that out, and add a Close button with unload me, then click btnDone and watch it's behaviour...  
0
 

Author Comment

by:tim_a_cook
ID: 13897078
AH HA!! I think we may be getting somewhere...I seperated it into three buttons. One that updates the DB, one that refreshes the listboxes, and one to Unloat Me. Click the Update button and of course nothing visibly happens, then click the refresh button and the record dissapears like its supposed to.
0
 
LVL 12

Expert Comment

by:Preece
ID: 13897112
Wooooohooooo!
0
 

Author Comment

by:tim_a_cook
ID: 13897119
oh my god i feel stupid...ok I got it to work...All I had to do was Close the recordset used to update the DB (the only one I didnt think to close) Close the DB Connection (RefreshServiceForm uses a different connection) then call RefreshServiceForm and BAM it works just like its supposed to...major ID10T Error on that one. Thanks a lot Preece

-TIMMY
0
 
LVL 12

Expert Comment

by:Preece
ID: 13897137
If you want to just have the Done button that takes care of all of it, then perhaps just put in a pause before calling unload me; something like:


psnPause 2

Private Sub psnPause(lSecs As Long)
    Dim dtTime As Date

    dtTime = Time
    Do Until DateDiff("s", dtTime, Time) > lSecs
        DoEvents
    Loop
End Sub
0
 
LVL 12

Expert Comment

by:Preece
ID: 13897144
No sweat!  Only close that connection if you have to.  Otherwise close it when you exit the app...

0
 

Author Comment

by:tim_a_cook
ID: 13897160
OK, Thanks again
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month20 days, 21 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question