Hi!

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.

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

    rst.Close
    cnn.Close
    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.AddNew
rst!CustomerID = Me.cboSelect
rst!CustomerName = Me.txtCustomerName
rst!Address = Me.txtAddress
rst!City = Me.txtCity
rst.Update

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

HTH,

Bob

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.


mmcdonal

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.

So...

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.AddNew
rst!CustomerID = Me.cboSelect
rst.Update

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.

Bob