Converting a Combo Box to a Text Box

[gaRed]

I have a form that deals with production data that has two combo boxes, one for the starting date and one for the finish date, as well as several radio buttons that give users the option to go back in week, month or quarter increments instead of going back to a specific start date.
Things were going well until my users requested the ability to type in a finish date. They want to be able to type in a week ending date and select the week radio button so that they get the data for an entire week, even if there is no data for the week ending date. I changed the combo box to a text box, but now I'm having trouble with the code to make it all work. Here's what I have.


'Snapshots*****************************************************************************************************
Private Sub cmdSnapshot_Click()
Dim rsStartDate, rsEndDate As DAO.Recordset
Dim strSQL As String
Dim MyWorkDate As Date
Dim RptName As String
Dim dSQL As String
Dim RptPath As String
Dim RptPeriod As String
Dim RptStart As String
Dim RptStartPeriod As String

strSQL = "SELECT [tblProd].[Date] FROM tblProd WHERE [tblProd].[Record_ID] = " & [Forms]![frmDateRange]![cboFinishDate]
Set rsEndDate = CurrentDb.OpenRecordset(strSQL, dbopensnapshot)
If rsEndDate.RecordCount > 0 Then
rsEndDate.MoveFirst
Else
Exit Sub
End If

If Not (IsNull([Forms]![frmDateRange]![cboStartDate])) Then
strSQL = "SELECT tblProd.[Date] FROM tblProd WHERE tblProd.[Record_Id] = " & [Forms]![frmDateRange]![cboStartDate]
Set rsStartDate = CurrentDb.OpenRecordset(strSQL, dbopensnapshot)
If rsStartDate.RecordCount > 0 Then
rsStartDate.MoveFirst
Else
Exit Sub
End If
End If


If Not (IsNull([Day])) Then
strSQL = "SELECT tblProd.*, tblCreel.* FROM tblProd INNER JOIN tblCreel ON tblProd.Record_ID = tblCreel.Record_ID "
strSQL = strSQL & " WHERE tblProd.[Date] BETWEEN #" & rsEndDate!Date & "# AND #"

Select Case Day
Case 1: MyWorkDate = DateAdd("d", -1, rsEndDate!Date)
RptStartPeriod = "1 Day" '**
Case 2: MyWorkDate = DateAdd("ww", -1, rsEndDate!Date)
RptStartPeriod = "1 Week" '**
Case 3: MyWorkDate = DateAdd("m", -1, rsEndDate!Date)
RptStartPeriod = "1 Month" '**
Case 4: MyWorkDate = DateAdd("m", -3, rsEndDate!Date)
RptStartPeriod = "1 Quarter" '**
Case 5: MyWorkDate = DateAdd("m", -6, rsEndDate!Date)
RptStartPeriod = "3 Quarters" '**
End Select
strSQL = strSQL & MyWorkDate & "#"
Else
strSQL = "SELECT tblProd.*, tblCreel.* FROM tblProd INNER JOIN tblCreel ON tblProd.Record_ID = tblCreel.Record_ID "
strSQL = strSQL & "WHERE tblProd.[Date] BETWEEN #"
strSQL = strSQL & rsEndDate!Date & "# AND #"
strSQL = strSQL & rsStartDate!Date & "#"
'RptStartPeriod = DatePart("m", [cboStartDate]) & "_" & DatePart("d", [cboStartDate]) & "_" & Right(DatePart("yyyy", [cboStartDate]), 2)
'RptStartPeriod = Forms![frmDateRange]![cboStartDate].Column(1)
'RptStartPeriod = DLookup("[tblMonths]![MonthName]", "[tblMonths]", "[tblMonths]![ID] = DatePart('m',[cboStartDate])&quot & "_" & DatePart("d", cboStartDate)
End If

If Shift1 Or Shift2 Or Shift3 Or Shift4 = Not Null Then
strSQL = strSQL & "AND ((tblProd.Shift)= [Forms]![frmDateRange]![Shift1] Or (tblProd.Shift)= [Forms]![frmDateRange]![Shift2] Or (tblProd.Shift)= [Forms]![frmDateRange]![Shift3] Or (tblProd.Shift)= [Forms]![frmDateRange]![Shift4])"
End If
SetMyReportSQL (strSQL)


DoCmd.Hourglass True
Application.Echo False, "Please Wait...............Now generating SnapShot"
RptPath = "C:\Creel\Reports\"
RptStart = DatePart("m", [cboFinishDate]) & "_" & DatePart("d", [cboFinishDate]) & "_" & Right(DatePart("yyyy", [cboFinishDate]), 2)
RptPeriod = DLookup("[tblMonths]![MonthName]", "[tblMonths]", "[tblMonths]![ID] = DatePart('m',[cboFinishDate])&quot & DatePart("d", cboFinishDate)
'RptPeriod = cboFinishDate
'RptPeriod = Me!cboFinishDate.Column(1)
RptName = "rptDetailByDay"

Application.Echo False, "Now outputting a detail report"
DoCmd.OutputTo acOutputReport, RptName, "Snapshot Format", RptPath & RptName & "_" & RptPeriod & "- " & Format(Me.cboStartDate.Column(1), "mm_dd_yyyy&quot & ".snp", False
'DoCmd.OutputTo acOutputReport, RptName, "Snapshot Format", RptPath & RptName & "_" & Format(Me.cboStartDate.Column(1), "mm_dd_yyyy&quot & "-" & Format(Me.cboFinishDate.Column(1), "mm_dd_yyyy&quot & ".snp", False

DoCmd.Hourglass False
Application.Echo True
'DoCmd.SetWarnings True

Exit_cmdSnapshot_Click:
Exit Sub

Err_cmdSnapshot_Click:
'MsgBox Error$
Resume Exit_cmdSnapshot_Click

End Sub


This is my last day at this job so I'm really stressed about finding a solution to this before the end of the day.
Thanks!

 

[JoanneM]

Hi there

if you have removed the combo box you have removed the ability to reference it - ![cboFinishDate] unless you have called the text box the same name.

if you want to quickly replace ![cboFinishDate]
just highlight the code cboFinishDate and ctrl H which will let you replace the cboFinishDate with txtFinishDate

compile your code and run

hope that helps

jo