Looking for input

B

Beetle

I'm hoping for some input from some of you Access gurus out there. I have a
form that includes two unbound combo boxes, cboOperators an cboWells. They
select info from tblOperators (PK is OperatorID) and tblWells (PK is WellID)
respectively. Each has a not in list event to add new values.

The problem is that cboWells is filtered based on the selection in
cboOperators. If a new well name is entered in cboWells, I want to check it
against the entire recordset of tblWells, not just the filtered records, in
case the name is assigned to another Operator. I don't want to set the
WellName field in the table to No Duplicates. I thought of using DCount to
see if a record exists in the table, but in addition to finding if a record
exists, I also want to be able to tell the user which Operator it is assigned
to. Then I would generate a message like;

"The well name you entered already exists but is assigned to Operator A. Do
you want to add this well name for Operator B also?"

I'll probably figure out a way to do it, but I tend to overcode sometimes,
so I was just hoping for some ideas on how some of you might do it.

Hopefully this makes sense and I'm not just babbling like a fool.
(is it still considered babbling if you're typing and not actually speaking?)

Thanks in advance for any advice.
 
P

Pieter Wijnen

In your WellID_NotInList()

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT OperatorID FROM tblWell Where
WellID='" & NewData & "'", DAO.dbOpenSnapshot)
If Rs.EOF Then
' Standard Question
Else
' Informative Question
End If
Rs.Close : Set Rs = Nothing
' etc
End sub

HTH

Pieter
 
B

Beetle

Pieter

Thanks for your reply. That's easier than whatever I probably would have
come up with, but I am still stuck on one thing.

After the recordset determines the OperatorID from tblWells (assuming a
matching record exists) then I need to pull the correct Company Name from
tblOperators based on the result of the recordset and insert that name into a
text box on a popup form. Something along the lines of;

If Not RS.EOF then
Dim strExistingName AS String

strExistingName = SELECT tblOperators.CompanyName FROM tblOperators WHERE
tblOperators.OperatorID = (the result of the recordset)

DoCmd.OpenForm "frmPopup"
Forms!frmPopup![txtExistingName] = strExistingName

I'm getting hung up on the WHERE clause. I don't know how to match
tblOperators.OperatorID to the OperatorID from the recordset. I was thinking
maybe I need to use Bookmark, but I'm not sure how to code it properly.

I would be thankful for any other advice you might have
 
P

Pieter Wijnen

Doesn't get much harder

Set Rs = CurrentDb.OpenRecordset("SELECT O.OperatorID, O.CompanyName FROM
tblOperator O " & _
"INNER JOIN
tblWell W ON O.OperatorID=W.OperatorID " & _
"Where W.WellID='"
& NewData & "'", DAO.dbOpenSnapshot)

HTH

Pieter


Beetle said:
Pieter

Thanks for your reply. That's easier than whatever I probably would have
come up with, but I am still stuck on one thing.

After the recordset determines the OperatorID from tblWells (assuming a
matching record exists) then I need to pull the correct Company Name from
tblOperators based on the result of the recordset and insert that name
into a
text box on a popup form. Something along the lines of;

If Not RS.EOF then
Dim strExistingName AS String

strExistingName = SELECT tblOperators.CompanyName FROM tblOperators WHERE
tblOperators.OperatorID = (the result of the recordset)

DoCmd.OpenForm "frmPopup"
Forms!frmPopup![txtExistingName] = strExistingName

I'm getting hung up on the WHERE clause. I don't know how to match
tblOperators.OperatorID to the OperatorID from the recordset. I was
thinking
maybe I need to use Bookmark, but I'm not sure how to code it properly.

I would be thankful for any other advice you might have

Pieter Wijnen said:
In your WellID_NotInList()

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT OperatorID FROM tblWell Where
WellID='" & NewData & "'", DAO.dbOpenSnapshot)
If Rs.EOF Then
' Standard Question
Else
' Informative Question
End If
Rs.Close : Set Rs = Nothing
' etc
End sub

HTH

Pieter
Click to expand...
Click to expand...
 
Top