checking whether a condition is true on a subform 1

[mattl72]

Hi all,

First post

I am working on developing an inventory database that tracks item and bin info.

I have a main form with the item no, description, etc. There is a datasheet subform on the main form that tracks bin no, bin loc, and a yes/no checkbox that tracks whether the bin is primary or not. I need to check is whether a particular item has at least one - but no more than one - primary bin checked (an item can have multiple bins). Is there anyway to do this in the form? Perhaps in the code?

Hope this makes sense. Thanks in advance for your help.

Matt L

 

[PaulBricker]

Matt, what do you want to do with the information when you find out? This code will give you message boxes telling you what's going on. You can use this in the Current Event for the Form. You may have to clean up the syntax in the SQL string but it should be close if ItemNo is a text value.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String

On Error GoTo ErrHandler
strSQL = "Select * From YourTable Where YourTable.ItemNo = '" & Forms!MainForm!ItemNo & "' and " & Forms!MainForm!SubformName!CheckBox = -1 & ""
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
rst.MoveLast
If rst.RecordCount = 1 Then
MsgBox "You only have one Primary Bin selected"
ElseIf rst.RecordCount > 1 Then
MsgBox "You have more than one Primary Bin selected"
ErrHandler:
If Err.Number = 3021 Then
MsgBox "There are no Primary Bins selected"
Else
MsgBox Err.Number & " " & Err.Description
End If

Set rst = Nothing

End If

Paul

 

[PaulBricker]

Oops found my own problem in the SQL string. I wish they had a way to edit your posts once you've posted them.


strSQL = "Select * From YourTable Where YourTable.ItemNo = '" & Forms!MainForm!ItemNo & "' and YourTable.CheckBoxName = " & Forms!MainForm!SubformName!

YourTable should be the table underlying your subform.

Paul

 

[PaulBricker]

Test string 3.

strSQL = "Select * From YourTable Where YourTable.ItemNo = '" & Forms!MainForm!ItemNo & "' and YourTable.CheckBoxName = -1"

One of these should work

Paul

 

[thornmastr]

I gather what you have is a master form subform relationship such that all records comprising the subform belong to the record found in the master form. Now, all you need to do is zip through the underlying recordset of the subform and do some counting of the bin number. It is Ok to have no bin numbers because you may add one of them now, or exactly one bin number, but no more than one bin number.

Obviously you want to make this test for every record associated with your primary form. Assuming the child container for your subform is called child0, you could use the following code in the oncurrent event of your master form. The variable gknt is an integer defined either global to your application or global to your form.

Dim rs as recordset

Gknt = 0
Set rs = me.child0.form.recordsetclone
If rs.recordcount < 1 then
Msgbox “WHOOPS. BIG TIME FAILURE. NO RECORDS”
Exit sub
Endif

With rs
,movefirst
while not .EOF
if !bin = true then
gknt = gknt + 1
endif
.movenext
wend
.close
end with
set rs = nothing

at this point, the variable gknt will tell you how many records in your subset have the bin flag set true.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[mattl72]

Actually, a message box is perfect. I just want to give them a warning that they need to enter a primary bin

I think I am headed in the right direction. I had to mess around with the syntax of the SQL statement a little but I think I got that to work. But now when I try to run the form, it comes back with a Compile error: User Defined Type is Not Defined. It is highlighting the &quot;dim db as dao.database&quot; line when it breaks. I'm not sure why access is having a problem with dao objects. It seems to be just that variable. Could I be missing a library or something?

 

[thornmastr]

Check your references and make sure you hqave referenced the DAO library. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com