Cancel Sub if combo null or = ""

  • Thread starter Thread starter JK
  • Start date Start date
J

JK

Can anyone tell me why this doesn't work? I want to prevent a user from
altering the combo if there are related records in another table. If there
are no related records, then it's ok if they change it. I'm trying to cancel
the sub (or whatever) if cboSupplierID is null or equal to "". But it doesn't
seem to be working; when it's a new record and I try to select a supplier in
my combo, I'm always asked if I want to per the last few lines of the code
posted below. I also posted the check inventory query that runs in case
that's somehow relevant. Thx!

Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset
Dim varRelate As Variant

' First, check to see that notes were added to the request details section
If IsNull(Me.SupplierID) Or (Me.SupplierID = "") Then
GoTo cboSupplier_Exit
End If

' Check for related child rows
' Get a pointer to this database
Set db = CurrentDb
' Open the test query
Set qd = db.QueryDefs("qryCheckRelateInventory")
' Set the company parameter
qd!PoNo = Me.PartID
' Open a recordset on the related rows
Set rst = qd.OpenRecordset()
' If we got rows, then can't delete
If Not rst.EOF Then
varRelate = Null
' Loop to build the informative error message
rst.MoveFirst
Do Until rst.EOF
' Grab all the table names
varRelate = (varRelate + ", ") & rst!TableName
rst.MoveNext
Loop
MsgBox "You cannot change the value of this combo box because you" &
Chr(13) & Chr(10) & "have related rows in " & _
varRelate & "." & Chr(13) & Chr(10) & "Delete these records
first, then you can change the supplier name.", _
vbOKOnly + vbCritical, "Attention!"
' close all objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing

' Cancel the delete
Cancel = True
Me.Undo
Exit Sub
End If
' No related rows - clean up objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' No related rows, so OK to ask if they want to delete!
If vbNo = MsgBox("Are you sure you want to change supplier " &
Me.cboSupplier.Column(1) & "?", _
vbQuestion + vbYesNo + vbDefaultButton2, "Attention!") Then
Cancel = True
Me.Undo
End If

cboSupplier_Exit:
Exit Sub

cboSupplier_Err:
ErrorLog Me.Name & "_cboSupplier", Err, Error
Application.Echo True
Resume cboSupplier_Exit


============================
Checks for related inventory records...

PARAMETERS [PoNo] Long;
SELECT DISTINCT "Inventory Transactions" AS Tablename, PurchaseOrderID
FROM InventoryTransactions
WHERE InventoryTransactions.PurchaseOrderID=[PoNo];
 
JK said:
Can anyone tell me why this doesn't work? I want to prevent a user from
altering the combo if there are related records in another table. If there
are no related records, then it's ok if they change it. I'm trying to
cancel
the sub (or whatever) if cboSupplierID is null or equal to "". But it
doesn't
seem to be working; when it's a new record and I try to select a supplier
in
my combo, I'm always asked if I want to per the last few lines of the code
posted below. I also posted the check inventory query that runs in case
that's somehow relevant. Thx!

Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset
Dim varRelate As Variant

' First, check to see that notes were added to the request details
section
If IsNull(Me.SupplierID) Or (Me.SupplierID = "") Then
GoTo cboSupplier_Exit
End If

' Check for related child rows
' Get a pointer to this database
Set db = CurrentDb
' Open the test query
Set qd = db.QueryDefs("qryCheckRelateInventory")
' Set the company parameter
qd!PoNo = Me.PartID
' Open a recordset on the related rows
Set rst = qd.OpenRecordset()
' If we got rows, then can't delete
If Not rst.EOF Then
varRelate = Null
' Loop to build the informative error message
rst.MoveFirst
Do Until rst.EOF
' Grab all the table names
varRelate = (varRelate + ", ") & rst!TableName
rst.MoveNext
Loop
MsgBox "You cannot change the value of this combo box because you"
&
Chr(13) & Chr(10) & "have related rows in " & _
varRelate & "." & Chr(13) & Chr(10) & "Delete these records
first, then you can change the supplier name.", _
vbOKOnly + vbCritical, "Attention!"
' close all objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing

' Cancel the delete
Cancel = True
Me.Undo
Exit Sub
End If
' No related rows - clean up objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' No related rows, so OK to ask if they want to delete!
If vbNo = MsgBox("Are you sure you want to change supplier " &
Me.cboSupplier.Column(1) & "?", _
vbQuestion + vbYesNo + vbDefaultButton2, "Attention!") Then
Cancel = True
Me.Undo
End If

cboSupplier_Exit:
Exit Sub

cboSupplier_Err:
ErrorLog Me.Name & "_cboSupplier", Err, Error
Application.Echo True
Resume cboSupplier_Exit


============================
Checks for related inventory records...

PARAMETERS [PoNo] Long;
SELECT DISTINCT "Inventory Transactions" AS Tablename, PurchaseOrderID
FROM InventoryTransactions
WHERE InventoryTransactions.PurchaseOrderID=[PoNo];
Click to expand...


So in this code block:
If IsNull(Me.SupplierID) Or (Me.SupplierID = "") Then
GoTo cboSupplier_Exit
End If
Click to expand...

.... the GoTo is not executed? Check to see if there is a Default Value
property specified for the SupplierID combo box, or for the SupplierID field
in the table (assuming the combo box is bound to such a field). If
SupplierID is a Long Integer field, the odds are good that it was created
with a default value of 0, and you need to remove that.
 
Nope - I checked that. I even tried linking the combo to a new text box and
combo box to see if anything was being stored in the field - I thought maybe
I couldn't see it.

Anyway, after a couple of hours on this annoying little issue, I figured out
a way around it. I simply removed the bit of code that asks the user if they
want to change the value in the field. If there is a related record, the
combo cannot be changed. If there is no related record, the user can change
the value.

Thx for your help though.. I appreciate it.


Dirk Goldgar said:
JK said:
Can anyone tell me why this doesn't work? I want to prevent a user from
altering the combo if there are related records in another table. If there
are no related records, then it's ok if they change it. I'm trying to
cancel
the sub (or whatever) if cboSupplierID is null or equal to "". But it
doesn't
seem to be working; when it's a new record and I try to select a supplier
in
my combo, I'm always asked if I want to per the last few lines of the code
posted below. I also posted the check inventory query that runs in case
that's somehow relevant. Thx!

Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset
Dim varRelate As Variant

' First, check to see that notes were added to the request details
section
If IsNull(Me.SupplierID) Or (Me.SupplierID = "") Then
GoTo cboSupplier_Exit
End If

' Check for related child rows
' Get a pointer to this database
Set db = CurrentDb
' Open the test query
Set qd = db.QueryDefs("qryCheckRelateInventory")
' Set the company parameter
qd!PoNo = Me.PartID
' Open a recordset on the related rows
Set rst = qd.OpenRecordset()
' If we got rows, then can't delete
If Not rst.EOF Then
varRelate = Null
' Loop to build the informative error message
rst.MoveFirst
Do Until rst.EOF
' Grab all the table names
varRelate = (varRelate + ", ") & rst!TableName
rst.MoveNext
Loop
MsgBox "You cannot change the value of this combo box because you"
&
Chr(13) & Chr(10) & "have related rows in " & _
varRelate & "." & Chr(13) & Chr(10) & "Delete these records
first, then you can change the supplier name.", _
vbOKOnly + vbCritical, "Attention!"
' close all objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing

' Cancel the delete
Cancel = True
Me.Undo
Exit Sub
End If
' No related rows - clean up objects
rst.Close
qd.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing
' No related rows, so OK to ask if they want to delete!
If vbNo = MsgBox("Are you sure you want to change supplier " &
Me.cboSupplier.Column(1) & "?", _
vbQuestion + vbYesNo + vbDefaultButton2, "Attention!") Then
Cancel = True
Me.Undo
End If

cboSupplier_Exit:
Exit Sub

cboSupplier_Err:
ErrorLog Me.Name & "_cboSupplier", Err, Error
Application.Echo True
Resume cboSupplier_Exit


============================
Checks for related inventory records...

PARAMETERS [PoNo] Long;
SELECT DISTINCT "Inventory Transactions" AS Tablename, PurchaseOrderID
FROM InventoryTransactions
WHERE InventoryTransactions.PurchaseOrderID=[PoNo];
Click to expand...


So in this code block:
If IsNull(Me.SupplierID) Or (Me.SupplierID = "") Then
GoTo cboSupplier_Exit
End If
Click to expand...

.... the GoTo is not executed? Check to see if there is a Default Value
property specified for the SupplierID combo box, or for the SupplierID field
in the table (assuming the combo box is bound to such a field). If
SupplierID is a Long Integer field, the odds are good that it was created
with a default value of 0, and you need to remove that.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
JK said:
Nope - I checked that. I even tried linking the combo to a new text box
and
combo box to see if anything was being stored in the field - I thought
maybe
I couldn't see it.

Anyway, after a couple of hours on this annoying little issue, I figured
out
a way around it. I simply removed the bit of code that asks the user if
they
want to change the value in the field. If there is a related record, the
combo cannot be changed. If there is no related record, the user can
change
the value.

Thx for your help though.. I appreciate it.
Click to expand...


You're welcome. Unsolved puzzles bother me, so if you want to look into it
further, post back.
 
Back
Top