Newbie needs help with form design

[dridge]

I have a data base with COMPANY NAME in it. I have a Combo box on my form with company name in the drop-down. How can I display a Yes/No field on my form based on what I select on the COMPANY NAME selection...Any help would be greatly appreciated!!

 

[RickSpr]

I'm not sure what you mean. You mean you want to display a check box that indicates whether the selected company name is/isn't in your table?

To do that, in the combo box's AfterUpdate event, construct a SQL statement with a WHERE clause that restricts the recordset to records with a matching company name. Then open a RecordSet object using the SQL statement. If the recordset is empty, set the value of the check box to True or False as desired, otherwise set it the opposite.

Sample code (assumes table name is MyTable, field name in it is CompanyName, combo box name is cboCompanyName, and check box name is chkCompanyFound):

    Dim SQL As String, db As Database, rst As RecordSet
    SQL = "Select CompanyName From MyTable WHERE CompanyName = """ & Me!cboCompanyName & """;"
    Set db = CurrentDB
    Set rst = db.OpenRecordset(SQL)
    If rst.EOF Then
        Me!chkCompanyFound = True
    Else
        Me!chkCompanyFound = False
    End If
    rst.Close
    Set rst = Nothing
    Set db = Nothing

Rick Sprague

 

[dridge]

I'm sorry, I explained it wrong.
I have a data base with COMPANY NAME (text 50)
and PAID (yes/No) in it. When I'm designing my form, I have COMPANY NAME in a combo box. After I select a COMPANY NAME in my combo, I want to display the Paid field on the form.

 

[dneufarth]

check here thread181-59670

Dave

 

[RickSpr]

Ok. What you want to do amounts to using the combo box to search for a record. When you do this, your combo box should be an unbound control. Using a bound control to both search for and update a record can screw up your data.

I'll assume your form is bound to your table containing the company name. Your form should have a bound check box for the Paid field, plus any other fields you might want to display.

There are 2 basic ways to proceed. Either one uses VBA code at the AfterUpdate event of the combo box. The first is to use the combo box value to construct a criteria string, which you then assign to the form's Filter property and activate by setting the form's FilterOn property to True. Example:

    Dim filt As String
    filt = "[CompanyName] = """ & Me!cboCompanyName & """;"
    Me.Filter = filt
    Me.FilterOn = True

The second method is a little more code, but I prefer it over using a filter because I like to leave control of the filter to the power users. In this method, you use a RecordSet variable to search for the value, then position the form's recordset to the location of the found record by setting its Bookmark property. Example:

    Dim rst As RecordSet
    Set rst = Me.RecordSetClone
    rst.FindFirst "[CompanyName] = """ & Me!cboCompanyName & """"
    Me.Bookmark = rst.Bookmark
    Set rst = Nothing

BTW, the FAQ that Neufarth suggested may be sufficient for your purpose, but it is not appropriate for a bound form. That is, if your users need to update the data, the FAQ method won't work for you. Rick Sprague

 

[7ony]

Examine the tables & query below. The query can be used in a form and is completely updatable except for the Paid field.
---
Table: Companies
Company (Text)
Paid (Yes/No)
Table: Invoices
InvID (Autonumber - Primary Key)
Company (Text)
Amt (Currancy)

Query:
SELECT Invoices.InvID, Invoices.Company, Invoices.Amt, Companies.Paid
FROM Invoices INNER JOIN Companies ON Invoices.Company = Companies.Company;
---
G'luck,
7ony