Loading a form Programmatically

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??
tim_a_cookAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PreeceCommented:
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
Dan Violet SagmillerCIS/Game Development AdvisorCommented:
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
tim_a_cookAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tim_a_cookAuthor Commented:
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
PreeceCommented:
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
tim_a_cookAuthor Commented:
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
tim_a_cookAuthor Commented:
guess its harder than i thought...the answer is now worth 500 points
PreeceCommented:
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
tim_a_cookAuthor Commented:
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
tim_a_cookAuthor Commented:
thanks preece, i'll give that a shot and let you know whether or not it worked
tim_a_cookAuthor Commented:
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
PreeceCommented:
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
PreeceCommented:
Also, have a look at the following ee question for more details on implementation:

http://www.experts-exchange.com/Programming/Q_21405277.html
tim_a_cookAuthor Commented:
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
jimbobmcgeeCommented:
>> 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.
tim_a_cookAuthor Commented:
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?
tim_a_cookAuthor Commented:
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
PreeceCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PreeceCommented:
Another thing, it is recommended that you close and destroy your recordsets when finished:

RSList1.Close
Set RSList1 = Nothing
PreeceCommented:
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....

tim_a_cookAuthor Commented:
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
PreeceCommented:
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.  
tim_a_cookAuthor Commented:
yeah i went through and closed all other recordsets and connections in the program...dont think i missed anything.
PreeceCommented:
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...  
tim_a_cookAuthor Commented:
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.
PreeceCommented:
Wooooohooooo!
tim_a_cookAuthor Commented:
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
PreeceCommented:
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
PreeceCommented:
No sweat!  Only close that connection if you have to.  Otherwise close it when you exit the app...

tim_a_cookAuthor Commented:
OK, Thanks again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.