How to put something that alerts the user to look at a certain field

[wwiSports]

I have a form that is based on an table "Athlete". The form is completly unbound, and everything is driven by vba.

The table has a "Notes" field in it. When entering data into the form you have the option to press F6 to open a seperate form and enter a note.

I would like to see what is the best way to note on the main form if there is something in the notes field (Is Not Null). For instance, maybe a box that appears for every athlete that has a note in their Note field.

Any suggestions?

Beth

 

[thornmastr]

Beth,

Create a label field and put your Look at notes string in that label. Make the label field invisible. If the notes field is not empty, set the label field to visible.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[wwiSports]

This is the following code on the form:

Private Sub Form_Load()

Dim AthID As String
Dim AskAth As Boolean
Dim dbs As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim strSQL As String
Dim x As Integer
FrmMode = Nz(Me.OpenArgs, "NEW&quot

Select Case UCase(FrmMode)
Case "ADD"
DSRcvdDate.Locked = True
InvoiceDate.Locked = False
DSRcvdDate.TabStop = False
InvoiceDate.TabStop = True
AskType = True
Case "NEW"
DSRcvdDate.Locked = False
InvoiceDate.Locked = True
DSRcvdDate.TabStop = True
InvoiceDate.TabStop = False
AskType = False
End Select

DoCmd.Maximize
Set dbs = CurrentDb
MainLoad
AskAth = GetSetting("WWSI", "Startup", "AskAth", "True&quot
If Not AskAth Then GoTo EndOfSub

Do
DtRcd = InputDtRcd
If DtRcd = "CANCEL" Then
SaveSetting "WWIS", "Defaults", "DSDt", ""
GoTo EndOfSub
Else
SaveSetting "WWIS", "Defaults", "DSDt", DtRcd
End If
Loop Until IsDate(DtRcd)
Do
AthID = InputAth
If AthID = "CANCEL" Then Exit Sub
Loop Until IsNumeric(AthID)

AthID = Format(AthID, "000000&quot
' Define search criteria.
strSQL = "AthleteID = '" & AthID & "'"
' Create a dynaset-type Recordset object based on Athlete table.
Set rst = dbs.OpenRecordset("Athlete", dbOpenDynaset)
' Find matching record.
rst.FindFirst strSQL
' Check if record is found.
If rst.NoMatch Then
rst.AddNew
rst!AthleteID = AthID
rst!DSRcvdDate = GetSetting("WWIS", "Defaults", "DSDt", "&quot
rst.Update
InvoiceNum = AthID & "001"
strSQL = "INSERT INTO invoice "
strSQL = strSQL & "(invoicenum, athleteid) VALUES ('"
strSQL = strSQL & InvoiceNum & "', '" & AthID & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
If AskType Then
OrdType = InputOrdType
If OrdType = "CANCEL" Then
OrdType = ""
GoTo EndOfSub
End If
Else
OrdType = "D"
End If
Me.Caption = "Order Form"
Me.AthleteID = AthID
PopulateFields AthID, 0, "stocknum1"
Me.LastName.SetFocus
Else
Me.Caption = "Order Form"
Me.AthleteID = AthID
Select Case UCase(FrmMode)
Case "ADD"
strSQL = "SELECT MAX (invoicenum) AS maxinv FROM QOrderQuery WHERE athleteid = '" & AthID & "'"
Set rst2 = dbs.OpenRecordset(strSQL)
If rst2.EOF Then
InvoiceNum = AthID & "001"
Else
InvoiceNum = Format(rst2!maxinv + 1, "000000000&quot
End If
OrdType = InputOrdType
If OrdType = "CANCEL" Then
OrdType = ""
GoTo EndOfSub
End If
strSQL = "INSERT INTO invoice "
strSQL = strSQL & "(invoicenum, athleteid, ordertype) VALUES ('"
strSQL = strSQL & InvoiceNum & "', '" & AthID & "', '" & OrdType & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
PopulateFields AthID, InvoiceNum, "stocknum1"
With Me
.ShipTo1 = UCase(.FirstName & " " & .LastName)
.ShipAddr1 = UCase(.Address1)
.ShipAddr2 = UCase(.Address2)
.ShipCity = UCase(.City)
.ShipState = UCase(.State)
.ShipZIP = .ZIP
End With
Me.PmtType.SetFocus
Case Else
InvoiceNum = AthID & "001"
PopulateFields AthID, 0, "stocknum1"

End Select
AthChange = True
InvChange = True
End If

'Use "Goto EndOfSub" instead of "Exit Sub" to make sure Access cleans up after itself
EndOfSub:
Set dbs = Nothing
Set rst = Nothing

End Sub

I have created the lable called lblSeeNotes and set it's visible property to no. My problem now is where I should put the If, Then, Else... statement in the above code.

If Me!Notes Is Null Then
Me!@lblSeeNotes.Visible = False
Else
Me!lblSeeNotes.Visible = True
End If

Beth

 

[wwiSports]

This is the following code on the form:

Private Sub Form_Load()

Dim AthID As String
Dim AskAth As Boolean
Dim dbs As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim strSQL As String
Dim x As Integer
FrmMode = Nz(Me.OpenArgs, "NEW&quot

Select Case UCase(FrmMode)
Case "ADD"
DSRcvdDate.Locked = True
InvoiceDate.Locked = False
DSRcvdDate.TabStop = False
InvoiceDate.TabStop = True
AskType = True
Case "NEW"
DSRcvdDate.Locked = False
InvoiceDate.Locked = True
DSRcvdDate.TabStop = True
InvoiceDate.TabStop = False
AskType = False
End Select

DoCmd.Maximize
Set dbs = CurrentDb
MainLoad
AskAth = GetSetting("WWSI", "Startup", "AskAth", "True&quot
If Not AskAth Then GoTo EndOfSub

Do
DtRcd = InputDtRcd
If DtRcd = "CANCEL" Then
SaveSetting "WWIS", "Defaults", "DSDt", ""
GoTo EndOfSub
Else
SaveSetting "WWIS", "Defaults", "DSDt", DtRcd
End If
Loop Until IsDate(DtRcd)
Do
AthID = InputAth
If AthID = "CANCEL" Then Exit Sub
Loop Until IsNumeric(AthID)

AthID = Format(AthID, "000000&quot
' Define search criteria.
strSQL = "AthleteID = '" & AthID & "'"
' Create a dynaset-type Recordset object based on Athlete table.
Set rst = dbs.OpenRecordset("Athlete", dbOpenDynaset)
' Find matching record.
rst.FindFirst strSQL
' Check if record is found.
If rst.NoMatch Then
rst.AddNew
rst!AthleteID = AthID
rst!DSRcvdDate = GetSetting("WWIS", "Defaults", "DSDt", "&quot
rst.Update
InvoiceNum = AthID & "001"
strSQL = "INSERT INTO invoice "
strSQL = strSQL & "(invoicenum, athleteid) VALUES ('"
strSQL = strSQL & InvoiceNum & "', '" & AthID & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
If AskType Then
OrdType = InputOrdType
If OrdType = "CANCEL" Then
OrdType = ""
GoTo EndOfSub
End If
Else
OrdType = "D"
End If
Me.Caption = "Order Form"
Me.AthleteID = AthID
PopulateFields AthID, 0, "stocknum1"
Me.LastName.SetFocus
Else
Me.Caption = "Order Form"
Me.AthleteID = AthID
Select Case UCase(FrmMode)
Case "ADD"
strSQL = "SELECT MAX (invoicenum) AS maxinv FROM QOrderQuery WHERE athleteid = '" & AthID & "'"
Set rst2 = dbs.OpenRecordset(strSQL)
If rst2.EOF Then
InvoiceNum = AthID & "001"
Else
InvoiceNum = Format(rst2!maxinv + 1, "000000000&quot
End If
OrdType = InputOrdType
If OrdType = "CANCEL" Then
OrdType = ""
GoTo EndOfSub
End If
strSQL = "INSERT INTO invoice "
strSQL = strSQL & "(invoicenum, athleteid, ordertype) VALUES ('"
strSQL = strSQL & InvoiceNum & "', '" & AthID & "', '" & OrdType & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
PopulateFields AthID, InvoiceNum, "stocknum1"
With Me
.ShipTo1 = UCase(.FirstName & " " & .LastName)
.ShipAddr1 = UCase(.Address1)
.ShipAddr2 = UCase(.Address2)
.ShipCity = UCase(.City)
.ShipState = UCase(.State)
.ShipZIP = .ZIP
End With
Me.PmtType.SetFocus
Case Else
InvoiceNum = AthID & "001"
PopulateFields AthID, 0, "stocknum1"

End Select
AthChange = True
InvChange = True
End If

'Use "Goto EndOfSub" instead of "Exit Sub" to make sure Access cleans up after itself
EndOfSub:
Set dbs = Nothing
Set rst = Nothing

End Sub

I have created the lable called lblSeeNotes and set it's visible property to no. My problem now is where I should put the If, Then, Else... statement in the above code.

If Me!Notes Is Null Then
Me!lblSeeNotes.Visible = False
Else
Me!lblSeeNotes.Visible = True
End If

Beth

 

[thornmastr]

Put it in the forms on current event so that it will check each record. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[wwiSports]

The OnCurrent Event is not an option on this form. It does not show up in the properties list.

??????

Beth

 

[thornmastr]

Beth,

I apologize. After scratching my head and looking at the original question I see”unbound form” and you are correct. The form has no way to know you have loaded it with information.

So now I guess you really want to know where to put that notes check.

Well, since you have to load every variable on that form yourself, I would check the value of notes of your record. If it has a value, you want to make that message visible. You probably would want to check after you fill in the last variable so the box will not come on before the first variable on the form gets filled in.

Also, check the available events for the notes field. If you have an after update event for that variable, and I think you do, you would also want to put it there as well because you want to know if the note has been deleted so you can make that box invisible, or, if it was empty and now is not, you want it to be visible.

If the event is not available, post back and we can come up with another idea or two or three.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[wwiSports]

Okay--I am going to try to put it at the end of the On Load event, that way it will be the last thing that the form tries to do, but I think there is a problem now with my code.

I didn't realize it at first, but you have to press f6 in order to bring the notes section up. The order form and the notes form are both based on the Athlete table, but both are unbound (I did not write this database obviously--just revamping two databases into one).

The code that I want to wrie in (above) wont work.

Ugh!!!!