Populate unbound textbox with data from sql query

[newbieX]

I have two tables (Company and Orders) that are linked 1-many. On the data entry form, there is a Continue button that when clicked grabs the contact information from the most recent order by the company selected from a combo box and re-queries the form's record source (q_Contacts) with this new record. The associated textboxes are then filled with the most recent contact information (Contact info is in Orders table and company name in Company table.)

I have several textboxes on the form that I want to populate with the contact information as in most cases this will information not change thus speeding up the tedium of data entry. Right now I am setting the form‘s record source to the query “q_Contacts” but this isn’t really what I want to do. I do not want the textboxes bound to the data in the Order table; just populated with it as the information on this form will be a new record. (Plus due to its 1-many relationship, I can't edit the textboxes anyway with the query.)

How do I query the tables and use the results of the query to populate the textboxes which need to be unbound? (They must remain unbound for reasons determined by powers that be higher in rank than me. It has to do with future plans for this database.")

My q_Contacts SQL:

SELECT Company.UID, Company.CompanyName, Orders.ContactLast, Orders.ContactFirst, Orders.ContactTitle, Orders.MailAdd, Orders.MailCity, Orders.MailState, Orders.MailZip, Orders.ContactPhone
FROM Company INNER JOIN Orders ON Company.UID = Orders.UID
GROUP BY Company.UID, Company.CompanyName, Orders.ContactLast, Orders.ContactFirst, Orders.ContactTitle, Orders.MailAdd, Orders.MailCity, Orders.MailState, Orders.MailZip, Orders.ContactPhone
HAVING (((Company.UID)=GetCompanyUID()));

I also have a module with the following code to grab the Company name value that was selected from the pull down menu before clicking “Continue”

Public varCompanyID As Long
Public Function GetCompanyUID() As Long
     GetCompanyUID = varCompanyID
End Function

Coding for Continue button:

Private Sub cmdContinue_Click()
      varCompanyID = cboCompanyName.Column(0)
      Me.Requery  ‘ Because the form’s recordsource is equal to the q_Contact query.  Not what I want.
End Sub

Suggestions as to how to approach this?

[jwhite]

Sounds like when [Continue] is clicked, run code to INSERT a new record with the contact info into the table and then open/goto that record for further editing. After the INSERT, use @@IDENTITY to identify the primary key of the record just added, for example: lngLastId = CurrentDb.OpenRecordset("select @@Identity from myTable")(0)

[June7]

Does company have more than one contact? If so, is there a field in Orders for the company ContactID? I don't think you want to insert a new contact record, just find existing contactID and pull data?

If there is only one contact for each company and all you need to save is CompanyID and display its associated contact info, options in order of my preference:

1. Include Company table in the form Recordsource, bind textboxes to the contact info fields and set them Locked Yes, TabStop No

2. multi-column combobox has the the extra info, columns can be hidden by 0" width, then expression in textbox references column index, index begins with 0

3. DLookup()

4. VBA code

[newbieX]

No. The contact info is tied to the order, not the company, which is why I grab the latest order for the selected company and populate the textboxes with the contact information from this last order. In most cases, the contact information does not change that often, but can. I thought about the multi-column option but couldn't figure out how to filter it to include just the latest order. Isn't there a way to grab this info and declare each field as a variable and make the variable the value for any given textbox?

[newbieX]

Doesn't that mean that the textboxes have to be bound to the table?

Sounds like when [Continue] is clicked, run code to INSERT a new record with the contact info into the table and then open/goto that record for further editing. After the INSERT, use @@IDENTITY to identify the primary key of the record just added, for example: lngLastId = CurrentDb.OpenRecordset("select @@Identity from myTable")(0)

[June7]

Sounds like you need a table of contacts. Instead of repeatedly saving the full contact info in Orders, save only the ContactID.

Otherwise, run code that finds the latest (most recent date?) order record for a company, opens a recordset object and pulls values from the recordset and populates order record fields.

The real trick is figuring out what event to put code in.

[newbieX]

I think maybe recordset object might be what I need. I have never used this feature before. Can I set my sql query to equal a temporary recordset and have this populate the textboxes? I assume the recordset object is "virtual" and separate from the table it was pulled from?

Research time!

Sounds like you need a table of contacts. Instead of repeatedly saving the full contact info in Orders, save only the ContactID.

Otherwise, run code that finds the latest (most recent date?) order record for a company, opens a recordset object and pulls values from the recordset and populates order record fields.

The real trick is figuring out what event to put code in.

[June7]

Yes, recordset is a 'virtual' object opened and manipulated in VBA. An alternative to recordset is DLookup() but that would require a DLookup for each value you want to return, less efficient.

[newbieX]

Recordset did the trick. Thanks for pointing me in the right direction.

[June7]

Wow! You must be really quick study. Glad you got it working.