Table update help please!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

Sorry if this is remedial, but I'm a little new to this.

I'm creating a form that will update several tables. At a given point in
navigating the form, the user will enter a value (Status) corresponding to
another value (Site).

At the point of the Status field Exit event, I'm trying to code an SQL that
asks the database if the value of the Site exists, then update the Status if
so. If not, create a new record with both.

My issue is it's not clear to me what method to use. Since I'm asking first
for a result from the initial statement "...where Site = [value of Site text
box]", I need a return from the SQL statement, which doesn't seem to be an
option with the DoCmd.OpenQuery method.

Do I then have to use a RecordSet method? I guess this seems like overkill
since I'm only questioning a local db.

I'm sure this will strike many here as a pretty egregious lack of
understanding of some fundamental functionality, but I really am stuck and
would greatly appreciate any help.

Thanks much in advance,
Paul
 
Hello Paul
You may like to have a look at DLOOKUP(<Fieldname>,<Tablename>,<Condition>)
For example you could use

Dim vRtn as variant

vRtn = DLookup("Status","Sites","[SiteID] = " & cboSiteID)

This assumes a table containing
SiteID, SiteName, Status.....

and a combo called cboSiteID which displays the site names but has the
SiteID as the hidden first column.
In the properties of cboSiteID:
cboSiteID.RowSource="SELECT [SiteID,SiteName] FROM Sites ORDER BY SiteName"
cboSiteID.Columns=2
cboSiteID.BoundColumn=1
cboSiteID.ColumnWidths="0cm;4cm"

If the site ID is not found vRtn will be null otherwise it will be the
Status of the Site.
HTH
Terry
 
Back
Top