Existence of a record

  • Thread starter Thread starter Jerry Anderson
  • Start date Start date
J

Jerry Anderson

I have a list box on a search form containing all possible bids in my
department. Ideally, there should be a corresponding detail of each bid on
another form, the detail form. Using the AfterUpdate method of the list box
on the search, it shoulld route to the detail form, with the correct record
loaded. My problem: If there is not a corresponding detail record, the detail
form, when it displays, is blank. How do I check for the existence of a
matching record before displaying the detail form so that I can gracefully
handle the error?
 
With a test on the count:

if 0 = DCount("*", "tableName", criteria_as_string ) then
... no record
else
... there is at least one existing record
end if



Vanderghast, Access MVP
 
Thanks, Michael. To better explain my problem, if a specific record doesn't
exist, I need to call a message box. The code I am unsing follows:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Bid Details"
stLinkCriteria = "[NUMBER]=" & "'" & Me![SelectBids] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
WHy don't you limit the listbox to only showing records that have details?
Are you using the listbox for something else?

Since I don't know your table structure I can't give you a precise answer, but
perhaps you would need the source for the listbox to be something like the
following:

SELECT DISTINCT Bids.BidId, Bids.BidName, Bids.DepartmentName
FROM Bids INNER JOIN BidDetails
ON Bids.BidID = BidDetails.BidID


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks, John. That's a great idea.
John Spencer said:
WHy don't you limit the listbox to only showing records that have details?
Are you using the listbox for something else?

Since I don't know your table structure I can't give you a precise answer, but
perhaps you would need the source for the listbox to be something like the
following:

SELECT DISTINCT Bids.BidId, Bids.BidName, Bids.DepartmentName
FROM Bids INNER JOIN BidDetails
ON Bids.BidID = BidDetails.BidID


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
Back
Top