Unbound Form Text Box is ShipDate--shows up with time

[wwiSports]

I have an unbound form called "Orders", which has about 5 Text Boxes (ShipDt1, ShipDt2, etc...). I have set the format as ShortDate on the form. My issue is that when I have someone's order up--if we have shipped their item to them,the date appears correctly, but in all of the other "ShipDt" fields the time 12:00:00AM appears.

I would like the ShipDt fields to be blank unless there is a date entered.

Another issue with these ShipDate fields is that when I open an order already there, we are not able to edit the ship date in their order, we have to close the form and go to the table to enter the ship date--this isn't a problem for me because I know how to do it, but it is a problem for the data entry people because they don't. We also shouldn't have to.

Any help on these two issues?

Beth

 

[Guest_imported]

Hi Beth,
I believe you will have somr button to bring up a paticular customer's order details. You could try the following code in the click event of that button:

Private Sub getOrderRecord_Click()

'Code to open the table and retrieve the records goes here

if Nz(rs.fields("date1&quot)="" then
Me.shipdt1=""
else
Me.shipdt1=rs.fields("date1&quot
endif
' Similar Code for the other date boxes goes here.
End Sub

As for not being able to edit them, check if you have accidentally set the Locked property of the text boxes to True. If this is the case, then you can programatically chnage the contents but not manually. So your text box will dysplay the date retrieved from the table but would not allow you to change it.

Hope this helps and let me know what happens.

 

[wwiSports]

There is a button to press to open the orders form. The code is as follows:

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
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
strSQL = "INSERT INTO invoice "
strSQL = strSQL & "(invoicenum, athleteid) VALUES ('"
strSQL = strSQL & InvoiceNum & "', '" & AthID & "');"
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

This form is completley unbound, so when it says Populate fields, there is more code there, if there is a current athlete id on file, it that code populates the fields. That would be the following code:

Private Sub PopulateFields(AthID As String, InvNum As String, FocFld As String)

ReDim aStockCost(1 To MaxItms) As Single
ReDim aStockSandH(1 To MaxItms) As Single
ReDim aInvLineNum(1 To MaxItms) As Integer
ReDim aQty(1 To MaxItms) As Integer
ReDim aStockDesc(1 To MaxItms) As String
ReDim aStockNum(1 To MaxItms) As Integer
ReDim aShipDt(1 To MaxItms) As Date
Dim strSQL As String
Dim dbs As Database
Dim MoreItems As Boolean

If AthChange Then SaveAthInfo
If InvChange Then SaveInvInfo

Set dbs = CurrentDb
If InvNum = "0" Or InvNum = "-1" Then
strSQL = "select * from QOrderQuery where athleteid = '" & AthID & "'"
Else
strSQL = "select * from QOrderQuery where athleteid = '" & AthID & "'"
strSQL = strSQL & " and invoicenum = '" & InvNum & "'"
End If
strSQL = strSQL & " order by athleteid, invoicenum, invlinenum"
' Debug.Print strSQL
Set AthRst = dbs.OpenRecordset(strSQL)
AthleteID = AthRst!AthleteID
FirstName = AthRst!FirstName
MiddleName = AthRst!MidName
Address1 = AthRst!Address1
Address2 = AthRst!Address2
City = AthRst!City
State = AthRst!State
ZIP = AthRst!ZIP
Email = AthRst!Email
PrimarySport = AthRst!PrimarySport
PrimaryEvent = AthRst!PositionEvent
ConfLtr = AthRst!ConfLtrDt
Biography = AthRst!Biography
Weight = AthRst!Weight
DSRcvdDate = AthRst!DSRcvdDate
'it is critical that the next several lines remain in this order *
InvoiceDate = AthRst!InvoiceDate '*
If InvoiceDate = "" Or IsNull(InvoiceDate) Then '*
If FrmMode = "NEW" Then '*
InvoiceDate = DSRcvdDate '*
Else '*
InvoiceDate = GetSetting("WWIS", "Defaults", "DSDt", "&quot
End If '*
End If '*
'end**************************************************************
InvOnHold = AthRst!InvOnHold
Gender = AthRst!Gender
GradYear = AthRst!GradYear
HeightFt = AthRst!HeightFt
HeightIn = AthRst!HeightIn
InvoiceNum = AthRst!InvoiceNum
If IsNull(InvoiceNum) Then
InvoiceNum = AthleteID & "001"
strSQL = "INSERT INTO invoice "
strSQL = strSQL & "(invoicenum, athleteid) VALUES ('"
strSQL = strSQL & InvoiceNum & "', '" & AthID & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End If
LastName = AthRst!LastName
Phone = AthRst!Phone
Pic = AthRst!Pic
PicID = AthRst!PicID
RespByDt = AthRst!RespByDt
CheckName = AthRst!CheckName
CardHolder = AthRst!CardHolder
CardNumber = AthRst!CardNumber
CardExpDate = AthRst!CardExpDate
PmtAmt = AthRst!PmtAmt
PmtCash = AthRst!PmtCash
PmtType = AthRst!PmtType
SchoolName = AthRst!SchoolName
SchoolState = AthRst!SchoolState
ShipTo1 = AthRst!ShipTo1
ShipTo2 = AthRst!ShipTo2
FirstName_LostFocus
ShipAddr1 = AthRst!ShipAddr1
CheckShip ShipAddr1, Address1
ShipAddr2 = AthRst!ShipAddr2
CheckShip ShipAddr2, Address2
ShipCity = AthRst!ShipCity
CheckShip ShipCity, City
ShipState = AthRst!ShipState
CheckShip ShipState, State
ShipZIP = AthRst!ShipZIP
CheckShip ShipZIP, ZIP
Notes1 = AthRst!Notes
If IsNull(Me.Notes1) Then
Me.lblSeeNotes.Visible = False
Else
Me.lblSeeNotes.Visible = True
End If

ItmsOnOrd = 0
NextItm = 0
SubTotal = 0
FirstShown = 1
MoreItems = True
Do While MoreItems
ItmsOnOrd = ItmsOnOrd + 1
NextItm = NextItm + 1
If IsNull(AthRst!InvLineNum) Then
aInvLineNum(NextItm) = 0
ItmsOnOrd = ItmsOnOrd - 1
Else
aInvLineNum(NextItm) = AthRst!InvLineNum
If aInvLineNum(NextItm) > NextItm Then
NextItm = aInvLineNum(NextItm)
End If
If aInvLineNum(NextItm) = CurrentLine Then
FirstShown = ItmsOnOrd - 1
End If
End If
aStockNum(NextItm) = Nz(AthRst!StockNum, 0)
aStockDesc(NextItm) = Nz(AthRst!StockDesc, "&quot
aQty(NextItm) = Nz(AthRst!Qty, 0)
aStockCost(NextItm) = Nz(AthRst!StockCost, 0)
aStockSandH(NextItm) = Nz(AthRst!StockSandH, 0)
aShipDt(NextItm) = Nz(AthRst!ItemShipDate, 0)
AthRst.MoveNext
If AthRst.EOF Then
MoreItems = False
ElseIf InvoiceNum <> AthRst!InvoiceNum Then
MoreItems = False
End If
Loop
'NextItm = NextItm + 1
If FirstShown < 1 Then
FirstShown = 1
StockNum1.SetFocus
End If
DisplayItems (FirstShown)

If ItmsOnOrd > 4 Then 'Enable the Down button if there are more than 4 items on this order
cmdNextItm.Enabled = True
cmdNextItm.Visible = True
Else
cmdNextItm.Enabled = False
cmdNextItm.Visible = False
End If

If FirstShown = 1 Then 'Disable the Up button if first item shown is first item on order
cmdPrevItm.Enabled = False
cmdPrevItm.Visible = False
Else
cmdPrevItm.Enabled = True
cmdPrevItm.Visible = True
End If

CalcTotal

CurrentLine = 0
Me(FocFld).SetFocus

End Sub 'PopulateFields

I would prefer to put whatever code in the Populate fields code, because that function is used throughout the form (Next Athlete, Previous Athlete, Add Athlete, Find Ahtlete)

None of the fields are locked. What is happening there, is it will allow me to change the date in the form, but the information is not saving--I think I now know how to fix that one though (Just now figured it out!) I'll let you know about that--if it does get fixed, and please let me know where the above code should go so I can get rid of the 12:00:00AM because it is annoying!!!