Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 14th, 2005, 09:07 AM
Registered User
Join Date: Feb 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help to fill forms quickly


I have a form called "Incoming e-mails". It contains several fields of different information and, therefore, when I'm creating a new record I have to fill (too) many fields by hand. I have created combo boxes to reduce typing in this task, but I wonder if there would be a sophisticated method to fill _many_ fields with only one selection of combo box, list box etc? E.g. there is a lot of information concerning customers who are sending e-mails (first name, last name, address, country, etc.) and all of this is documented in particular table in our database. I would appreciate the answer, as I'm tired to riffle through the manual pages.

Old February 14th, 2005, 10:51 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Easiest way would be to use an unbound form. Fill a combo box with, say, Customer IDs, then open an ADO recordset based on your customer table, use the ADO Find method to move to the correct record, than populate controls on the form from the current record in the recordset.

Private Sub cboSelect_AfterUpdate()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strSearch As String

    Set cnn = CurrentProject.Connection

    Set rst = New ADODB.Recordset
    strSQL = "SELECT * FROM tblCustomers"
    rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

    strSearch = "[CustomerID] = " & " '" & Me![cboSelect] & "'"
    rst.Find strSearch

    Me.txtCustomerName = rst!CustomerName
    Me.txtAddress = rst!Address
    Me.txtCity = rst!City

    Set rst = Nothing
    Set cnn = Nothing
End Sub

Call the recordset's AddNew and Update methods if you'd like to add the new record:

rst!CustomerID = Me.cboSelect
rst!CustomerName = Me.txtCustomerName
rst!Address = Me.txtAddress
rst!City = Me.txtCity

Course, your cutomer table would need to use a primary key of something other than CustomerID here, but you get the idea.



Old February 15th, 2005, 09:25 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Okay, just looking in, and I like the solution, but I didn't understand the last line:

"...your cutomer table would need to use a primary key of something other than CustomerID here..."

I am not sure you why you say that. The query is using CustomerID as the PK, and that is the perfect PK name for tblCustomer. The combo box is going to be bound to the PK, in this case CustomerID.

Am I missing something? I am an intermediate vba'er.

Old February 15th, 2005, 10:38 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi mmcdonal,

I tossed in the ADO update stuff simply to show how its done without giving much thought as to how khytonen might use it. An adOpenStatic, adLockReadOnly recordset isn't updateable anyway. Sorry for the vagueness. Got lazy.

I included it because I was speaking about using unbound forms and controls (no Record Source or Control Source databinding properties set for forms or controls, respectively). Unbound forms used to add records require that the insert be done through code. Hence, the AddNew code block demonstration after thought.


quote:The combo box is going to be bound to the PK...
isn't happening. The combo box is unbound also (no ControlSource property set). It only serves as a sort of "query criteria viewer".

About the primary key thing, though. Say the Row Source property of the combo box is set to: "SELECT * FROM tblCustomers" and CustomerID is the primary key. Then you open a recordset based on tblCustomers and search for a record:

strSearch = "[CustomerID] = " & " '" & Me![cboSelect] & "'"
rst.Find strSearch

This brings you to the record in the recodset that satifies the criteria. Then:

rst!CustomerID = Me.cboSelect

will simply try and take that primary key value and put it back in tblCustomers. The value already exists as a primary key value in tblCustomers. So you get a duplicate primary key violation.

The point of the post was simply to show how to bind unbound textbox controls on an unbound form to an ADO recordset based on a value in an unbound combo box. Feel free to ingnore the PK comment. Thats just how I set up my test module.


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to fill the IE's web page forms from VB? JohnThomas VB How-To 1 April 19th, 2009 06:53 AM
Database grows too quickly. 99mary BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 April 5th, 2007 08:02 AM
Please help me quickly deep Java Basics 0 June 17th, 2006 11:59 PM
How do I quickly find version numbers for A-M-P? ababb BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 2 January 11th, 2005 02:26 PM
Auto-fill acrobat forms with databases ... austinglen Classic ASP Databases 0 January 4th, 2004 08:59 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.