Disable cmdButton if record doesn't exist

  • Thread starter Thread starter Mike Revis
  • Start date Start date
M

Mike Revis

Hi Group,
Access 2000. WinXPPro

On frmJob I have cmdAir(opens frmAir), cmdOcean(opens frmOcean) and
cmdPackage(opens frmPackage).

Each form is based on its own table.

I am trying to keep cmdPackage disabled until either tblAir or tblOcean has
had data entered relating to JobID.

I can have cmdPackage disabled when it is a new record but I can't figure
out how to check tblAir and tblOcean for JobID when it's not a new record.

I think I know that I have to check the records in tblAir and tblOcean for
the existence of JobID and I have looked at recordset help but I'm not
getting it.

As always suggestion and guidance is appreciated.

Mike
 
you could create a Union query to "combine" the applicable records from
tblAir and tblOceans, as

SELECT tblAir.JobID
FROM tblAir
WHERE tblAir.JobID = Forms!frmJob!JobID
UNION SELECT tblOcean.JobID
FROM tblOcean
WHERE tblOcean.JobID = Forms!frmJob!JobID;

then, in the form's Current event procedure, use a DCount() function to
check for a record related to the current record on frmJob, as

Public Sub CheckAirOcean()

If DCount(1,"UnionQueryName") > 0 Then
Forms!frmJob!cmdPackage.Enabled = True
Else
Me!cmdPackage.Enabled = False
End If

End Sub

Private Sub Form_Current()

CheckAirOcean

End Sub

the above code assumes that the primary key field the table underlying
frmJobs is JobID, and the foreign key fields in both tblAir and tblOcean is
also called JobID. if not, substitute the correct field names.
you could also do this with a recordset, or two, or use two DCount()
functions run directly against each table, but i figured using the Union
query would be easier.

hth
 
Back
Top