Results 1 to 7 of 7

Thread: Unbound Forms

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Unbound Forms

    I have an Main input form which is Unbound and a have a Search form which has a LstBox on. My problem is that I have 3 fields in my LstBox and when selected the record then shows on my main form. But only the UserID shows and not the UserID, Name & Username.

    How can my main form show all 3 records and not just the 1?

    This is code from my LstBox select
    Code:
    Private Sub lstAllLogs_DblClick(Cancel As Integer)
        If IsNull(Me.lstAllLogs) Then
            MsgBox "Select from the list", vbExclamation
            Exit Sub
        End If
        Form_frmUserLogs.InputAllLogIn Me.lstAllLogs
        Form_frmShowLogIn.Visible = False
    End Sub
    This is the code on the Main Form
    Code:
    Sub InputAllLogIn(intUsrID As String)
    Dim sQRY As String
    '**************************************
            sQRY = _
                "SELECT dbo_UserLog.UserID, dbo_UserLog.Username, dbo_UserLog.Name, dbo_UserLog.DateTimeLogIn, dbo_UserLog.AuthLevel " & _
                "FROM dbo_UserLog " & _
                "WHERE dbo_UserLog.UserID = Forms!ShowLogIn!lstAllLogs "
        With Me
            .RecordSource = sQRY
            .txtUserID = intUsrID
        End With
    End Sub
    How can I show all records in my TxtBox's on my main form?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you though of assigning the other values you have retrieved form your query to the relevant text boxes?

    after all you are assigning the user id to txtUserID....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Yes, thought of that aft I had just posted...

    on my routine
    Code:
    Sub InputAllVisits(intNHSNo As String)
    does it make any difference if I do this
    Code:
    Sub InputAllVisits()
    Dim intNHSNo As String
    also is it ok to do this
    Code:
    Dim intUsrName, intName As String
    Last edited by JezLisle; 11-21-08 at 06:33.

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have tried this below and it this doesnt pull the data back, is this correct or should have done it another way?

    Code:
    Sub InputAllLogIn(intUsrID As String)
    Dim sQRY As String
    Dim intUsrName As String
    Dim intName As String
    '**************************************
            sQRY = _
                "SELECT dbo_UserLog.UserID, dbo_UserLog.Username, dbo_UserLog.Name, dbo_UserLog.DateTimeLogIn, dbo_UserLog.AuthLevel " & _
                "FROM dbo_UserLog " & _
                "WHERE dbo_UserLog.UserID = Forms!ShowLogIn!lstAllLogs "
        With Me
            .RecordSource = sQRY
            .txtUserID = intUsrID
            .txtUsrName = intUsrName
            .txtName = intName
        End With
    End Sub
    or is there away of doing it by DLOOKUP, I havent used that before so not sure on that

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the with me construct looks wrong in this context

    you are attempting to assign a recordsource, yet you claim you are using unbound forms
    the with notation is OK.. but I think it tends to lead to sloppy thinking.. I prefer to explicitly references any properties, methods whatever from objects

    I'd expect to see something like
    txtUserID= myunboundrecorsource.columnname

    I think you are missing a step between defining your query and assigning the values.. ie you haven't actually retrieved the data. you need to look at how you will retrieve the data, ie whether you use ADO or DAO.. have a look at the the open method.

    dlookup will retrieve a single value (admittedly uyou can stuff several columns into that value and then extract them before use). its fine for the occasional action.. but I wouldn't use it code that is going to run frequently or run in a tight loop.. its costs a lot to open up a domain function, whereas opening a connection for an ADO or DAO recordset happens only once, and you can reuse that connection with multiple SQL sattements.. but YOU MUST remember to close that recordset & connection when you are done otherwise there is a risk that you will leak memory

    eg select column1 & "+" & column2 & "+" & column3 as mydata from mytable

    then split() that value...

    I think you may need to consider if you need to go on some training course to learn about Access, something which is beyond the scope of this site...... its intended to help and assist developers, not teach from the basics. getting a good reference book would be a smart move.

    also getting to grips with debugging and the Access help system (however compromised that is these days)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    To answer your question about Dim

    Dim intUsrName, intName As String

    is not the same as

    Dim intUsrName as String
    Dim intName As String


    which I'm assuming is what you mean. When no type is specified, the default Data Type is Variant. So

    Dim intUsrName, intName As String

    is the same as

    Dim intUsrName as Variant
    Dim intName As String
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Healdem

    The courses I have been on a far too basic and wont go into what programming is about.

    Taking on board what you said in Thread5 this is what I have now got. It now brings the record through. A problem I do have is that it will always bring in the 1st record if there are multiple records on the same UserID (because of the LogIn Times)
    How is it posible to select the one I want and for it not be the 1record showing on my main form?
    Code:
    Sub InputAllLogIn(intUsrID As String)
    Dim sQRY As String
    Dim rs As ADODB.Recordset
    '**************************************
        Set rs = New ADODB.Recordset
        Call UnLockAll
            sQRY = _
                "SELECT dbo_UserLog.UserID, dbo_UserLog.Username, dbo_UserLog.Name, dbo_UserLog.DateTimeLogIn, dbo_UserLog.AuthLevel " & _
                "FROM dbo_UserLog " & _
                "WHERE dbo_UserLog.UserID = '" & intUsrID & "' "
            rs.Open sQRY, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        DoCmd.SetWarnings False
        Me.txtUserID = intUsrID
        Me.txtUsrName  = rs.Fields("UserName")
        Me.txtName = rs.Fields("Name")
    and so on with all the Controls on the form
        rs.Close
        Set rs = Nothing
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •