Firing Blanks...

ozPATT · January 19th, 2006, 06:55 AM

hi everyone,

I have managed to get by over the last couple of years by knowing only basic sql. However, following a change in job role, it is becoming more and more necessary to know more complex sql.

today's problem is that i need to pull information from 3 different tables, and use that information to populate fields on a form. I have done some hunting around the internet to try and find how to select information from multiple tables using only one query, and I think I have it.

When I run the sql, i receive no errors, which has to be a good start, but it doesn't return any information. Can someone see what I am doing wrong here?

Code:

 'locals
    Dim strSQL As String
    Dim rst As Recordset

    'write sql
    strSQL = "SELECT t1.customer, t1.start, t1.finish, t2.costHotel, t2.costFlight, t2.mngrsNights, t2.hrsPerDay, t2.hrsOnSite, t3.countServer, t3.countWorkstation, t3.countLaptop, t3.countPrinter, t3.userRatio, t3.devicesPerSwitch, t3.serversPerRack FROM ProjectInfo t1, GeneralInfo t2, Devices t3 WHERE t1.id = " & intProjID & " AND t2.pid = " & intProjID & "AND t3.pid = " & intProjID & ";"

    'set recordset
    Set rst = New ADODB.Recordset

    'open rst
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    'variables
    Dim strCustomer As String
    Dim dtProjectStart As Date
    Dim dtProjectFinish As Date
    Dim intCostHotel As Integer
    Dim intCostFlight As Integer
    Dim intMngrsNights As Integer
    Dim intHrsPerDay As Integer
    Dim intHrsOnSite As Integer
    Dim intCountServer As Integer
    Dim intCountWorkstation As Integer
    Dim intCountPrinter As Integer
    Dim intCountLaptop As Integer
    Dim intUserRatio As Integer
    Dim intDevicesPerSwitch As Integer
    Dim intServersPerRack As Integer

    'set variables
    strCustomer = rst("customer")
    dtProjectStart = rst("start")
    dtProjectFinish = rst("finish")
    intCostHotel = rst("costHotel")
    intCostFlight = rst("costFlight")
    intMngrsNights = rst("mngrsNights")
    intHrsPerDay = rst("hrsPerDay")
    intHrsOnSite = rst("hrsOnSite")
    intCountServer = rst("countServer")
    intCountWorkstation = rst("countWorkstation")
    intCountPrinter = rst("countPrinter")
    intCountLaptop = rst("countLaptop")
    intUserRatio = rst("userRatio")
    intDevicesPerSwitch = rst("devicesPerSwitch")
    intServersPerRack = rst("serversPerRack")

as i say, no errors, but no information. any help would be greatly appreciated.

thanks

Patrick

Visit my site: http://www.drybonesuk.com

mmcdonal · January 19th, 2006, 09:30 AM

I think this is your problem:

"& intProjID ..."

Where is this integer coming from? You have no code to supply this value, and so the recordset would be empty since if it is being typed as an integer, it equals 0, and if as a string it equals "".

I would suggest packaging this query as a query, and then running the query rather than doing all this with code. Then making the query the record source for the form. If you need to pass parameters to limit the record source, then do that from another form.

HTH

mmcdonal

ozPATT · January 19th, 2006, 09:39 AM

hi there, thanks for the reply. the intProjID gets set as a global variable when they select the project they want to work with...

actually, thanks to your reply, I have checked the intProjID and it was referencing the wrong thing. I have since amended it and now it pulls out information. :D thanks!

Visit my site: http://www.drybonesuk.com

ozPATT · January 20th, 2006, 05:25 AM

i have another problem now regarding the code above. The recordset is populated now fine with the data from the database, however when it comes to populating the form controls, nothing seems to register. Basically, I have a main form, and that contains several subforms. each sub form has a tag and only one subform is displayed at a time depending on which button is pressed on the main form. (if that makes sense...)

the extra code that I have to display the information is as follows...

Code:

    'set form
    Set frmInfo = New Form_frmInfo

    'set fields
    frmInfo.txtGeneralCustomerName.Value = strCustomer
    frmInfo.txtGeneralProjectName.Value = strProject
    frmInfo.txtGeneralVersion.Value = strVersion
    frmInfo.dtpGeneralProjectStart.Value = dtProjectStart
    frmInfo.dtpGeneralProjectFinish.Value = dtProjectFinish

it all seems to reference fine, but for some strange reason, the values of the controls are not populated.

does anyone have any idea what I am doing wrong?

Thanks

Patrick

Visit my site: http://www.drybonesuk.com

Bob Bedell · January 20th, 2006, 08:40 AM

Just as a general comment, none of your data item variable declarations are necessary. You can simply load your controls directly from the recordset:

Sub LoadForm(frm As Form)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "File Name=" & CurrentProject.Path & "\DevelopDB.UDL"
    cnn.Open

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open "SELECT * FROM tblRecords", cnn, adOpenStatic, adCmdText
    Set rst.ActiveConnection = Nothing
    cnn.Close
    Set cnn = Nothing

    'Load controls directly from recordset.
    With frm
        .txtID = rst!ID
        .txtField1 = rst!Field1
        .txtField2 = rst!Field2
        .txtField3 = rst!Field3
    End With

    rst.Close
    Set rst = Nothing

End Sub

Bob

ozPATT · January 20th, 2006, 09:00 AM

hey thanks so much, i am a complete novice at all this kind of thing, so to be honest, i am a bit out of my league with the project I have taken on board, but it seems to be going ok so far...

so, i have taken out the variable declarations, as they didnt need to be there, and i have used 'with form' to save me some typing, i have also removed the .value as it looks as though i can just name the control and it will take care of the rest... correct?

but... i still dont have any variables in the controls when the function runs. what could i be doing wrong? is it because the form is in the main form? I wouldnt have thought that this would cause a problem, but then i dont know too much yet, so it could be that. i cant think of anything else it can be...

Visit my site: http://www.drybonesuk.com

Bob Bedell · January 20th, 2006, 10:02 AM

Try:

With frm!subForm.Form
  .txtID = rst!ID
  .txtField1 = rst!Field1
  .txtField2 = rst!Field2
  .txtField3 = rst!Field3
End With

with subForm being the name of your sub form. This will get you a reference to the controls on the sub form, provided that "Form_frmInfo" is the name of your main form. If it isn't, assign the name of your main form to the object variable, and the code should work.

The reason you don't need to specify .Value is because .Text is the default property of a text box. .txtID = rst!ID assigns a value to the text property. One of Access's annoying quirks is that if you specify the .Text property explicitly, you must first set focus to the control before you can set it. You can get around that by using .Value, or just using the control name with the implicit default property (Text).

HTH,

Bob

Bob Bedell · January 20th, 2006, 10:16 AM

I take that back.

.Value is the default property of a text box. So .txtField1 = rst!Field1 assigns a value to the Value property. Hence, .txtField1.Value = rst!Field1 just uses the default property explicitly.

.txtField1 = rst!Field1 is the same thing as .txtField1.Value = rst!Field1

Few...

The bit about needing to set focus to a text box before you can set its text property if you use .Text still holds.

Bob

ozPATT · January 20th, 2006, 10:37 AM

cool, i think i am getting somewhere... unfortunately not quite there yet... i have the following now:

Code:

With frmMain!SubForm.Form
        .txtGeneralCustomerName = rst("customer")
        .txtGeneralProjectName = strProject
        .txtGeneralVersion = strVersion
        .dtpGeneralProjectStart = rst("start")
        .dtpGeneralProjectFinish = rst("finish")
    End With

when i run it i get error 2465, MS Access can't find the field 'SubForm' referred to in your expression. If I replace 'SubForm' with frmInfo, which is the name of the form that I have place within frmMain, it runs through with no errors, but closes the form. No idea why this happens, but at least it is doing something which is more than it was before!

Visit my site: http://www.drybonesuk.com

Bob Bedell · January 20th, 2006, 11:01 AM

Might be because frmInfo is also the name of your object variable and when your procedure terminates, frmInfo goes out of scope and is destroyed, taking the instance of the form it refers to off the heap as well.

I'm passing the main form to the sub by reference in the main form's Load event:

Code:

Private Sub Form_Load()

    Call LoadForm(Me) <-- reference to main form

End Sub

I don't use an additional object variable in the sub, just the Form object parameter:

Code:

Sub LoadForm(ByRef frm As Form)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strEmployees As String

    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "File Name=C:\DevelopDB.UDL"
    cnn.Open

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
    rst.Open "SELECT * FROM tblRecords", cnn, adOpenStatic, adCmdText
    Set rst.ActiveConnection = Nothing
    cnn.Close
    Set cnn = Nothing

    'Load controls directly from recordset.
    With frm!frmInfo.Form
        .txtID = rst!ID
        .txtField1 = rst!Field1
        .txtField2 = rst!Field2
        .txtField3 = rst!Field3
    End With

    rst.Close
    Set rst = Nothing

End Sub

Bob