Solved

Populating Form controls with the data from a Table

Posted on 2007-11-15
16
272 Views
Last Modified: 2013-11-28
I have a need to fill in values in Input Box controls on a Form.  A user can fill those in manually but I need to make it easier for the user. So depending on the username the fields will be automatically filled when a form is opened. Now there is a Default Value field in the Properties Box of the Input Box control and I tried to fill in a select query in this field as follows: =SELECT value FROM settings WHERE parameter='accountname' AND username='Smith'; This does not work unfortunately, so I need help solving this task through Default Value field or writing VBA code. In the latter case the provision of the code is appreciated. Thanks
0
Comment
Question by:JakeAnvil
  • 8
  • 8
16 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20289812
Codewise (see code snippet):
    Public Sub Form_Load()

        Me.my_txt_box.Value = CurrentProject.Connection.Execute("SELECT value FROM settings WHERE parameter='accountname' AND username='smith';").Collect(0)

    End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20289823
Hmm... not very easy to read in such a small block!!! Here is is again:

    Public Sub Form_Load()
        Me.my_txt_box.Value = CurrentProject.Connection.Execute("SELECT value FROM settings WHERE parameter='accountname' AND username='smith';").Collect(0)
    End Sub
0
 

Author Comment

by:JakeAnvil
ID: 20298859
Compiler returns an error as there is no such class member as Collect() in my libraries. Can you pls confirm this works for you and what library contains the corresponding definition? Thank you!
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20299580
I can't see a problem with the code I've posted. Could you post what you have so I can check it. I'm using Access 2003.
0
 

Author Comment

by:JakeAnvil
ID: 20300508
Unfortunately I have this project in the office only and I do not remember the exact error message. After hitting compile an error popped up on the line that you supplied (the line that does the assignment of the value of the Input Box control in the On Load sub of the form, I changed the name of the form control to that of the desired Input Box) saying something like no class found or class unavailalble. Also the Autosuggest-as-you-type feature in the VBA code editor suggested Connection and Execute but not Collect as parts of the CurrentProject. I use Access 2003, too, but the difference is in the linked references I think - different versions of libraries.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20311085
I use exactly the form of the line I posted successfully in my code which is why I requested to see you line incase I spot a difference.

Are you perhaps connecting to an external database?
0
 

Author Comment

by:JakeAnvil
ID: 20312114
The error is:
Compile error: Method or data member not found.
The code is:
Private Sub Form_Load()
    Me.CmbPortfo.Value = CurrentProject.Connection.Execute("SELECT value FROM settings WHERE parameter='defaultportfo';").Conect(0)
End Sub
Code looks fine but maybe I am wrong...
0
 

Author Comment

by:JakeAnvil
ID: 20312439
It is an internal Access table that I need to retrieve the data from.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:JakeAnvil
ID: 20312505
Pls disregard my post above ID: 20312114
The error is:
Run-time error '-2147221164 (80040154)': Class not registered
The code is:
Private Sub Form_Load()
    Me.CmbPortfo.Value = CurrentProject.Connection.Execute("SELECT value FROM settings WHERE parameter='defaultportfo';").Collect(0)
End Sub
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20313487
Can't see anything wrong with that code. I am using the following references:

    Visual Basic For Applications
    Microsoft Access 11.0 Object Library
    OLE Automation
    Microsoft DAO 3.6 Object Library
    Microsoft ActiveX Data Objects 2.1 Library
    Microsoft Office 11.0 Object Library
    Microsoft Excel 11.0 Object Library

I would imagine it would be one of these missing:

    Microsoft DAO 3.6 Object Library
    Microsoft ActiveX Data Objects 2.1 Library

Have you got them?
0
 

Author Comment

by:JakeAnvil
ID: 20313708
I got all the same libraries - this is getting weird...
Can you pls paste in here the Access help page text on this Collect method? My help doesnt recognise it...
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20315108
Very very strange!!! It's almost impossible to find anything about it on Google. I am using the code sucessfully though!!!

Anyway, seems we've got a bit caught up with this method. Here's an alternative (you might need to check the syntax):

    Public Sub Form_Load()
        Dim db As DAO.Database, rs As DAO.RecordSet, strSQL As String
       
        Set db = CurrentDb
        strSQL = "SELECT blah blah blah;"
        Set rs = db.OpenRecordSet(strSQL)
       
        If rs.RecordCount > 0 then
            rs.MoveFirst
            Me.my_text_box.Value = rs.Fields(0)
            ' or you can choose the field by name:
            ' Me.my_text_box.Value = rs.Fields("my_field_name")
        Else
            ' Some code incase there is nothing returned by the SQL
        End If
       
        ' Release memory
        Set rs = Nothing
        Set db = Nothing
    End Sub
0
 

Author Comment

by:JakeAnvil
ID: 20322954
When I came up with the task first thing I started building a function with exactly the same logics you just suggested. The function was called in On Load event but thought there is a shorter way (though I was hoping for the textbox settings dialog 'tweak' - not code). I really got thrilled with the one-liner you suggested so I am up to finding out the bottom of it. There is no much interest in the longer alternative and I would appreciate you helping me with the Collect method. Maybe you can send me a sample Access db where this method works for you?
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20326968
I'll try to make an example db, but am a little busy just now. In the mean time perhaps try this alternative:

    Me.my_text_box.Value = CurrentDb.OpenRecordSet("SELECT myfield blah blah").Fields(0)
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 300 total points
ID: 20327088
And this might also help:
http://allenbrowne.com/casu-07.html

    Me.my_text_box.Value = DLookup("value ", "settings ", "parameter= 'accountname' ")

or you might even be able to enter this directly into the value field in the properties window:
    =DLookup("value ", "settings ", "parameter= 'accountname' ")
0
 

Author Comment

by:JakeAnvil
ID: 20342319
Thank you very much - DLookup worked for me when input in the properties window of the textbox. This is essentially the short and effective solution that I was looking for.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now