help with "DoCmd.OpenForm"

[mtimbol]

Please help me...

I have a form which is made up of:

Main Form: Company Profile Form
Fields: Company Name, Address, Phone no.

Sub Form: Transactions Form
Fields: Company, Transaction Date,Transaction Type, Amount.

I want to make a main form containing the fields from The Company Profile Table with a subform
in datasheet view containing the fields from the Transaction Table. They are linked by
the Company field.

But I want the the user to be prompted for the inclusive dates of the transactions
every time the form is opened.

I tried using the Between [start date] and [end date] by making underlying queries.
Unfortunately I get prompted every time I move to the next record.

I've been trying to learn how to use the DoCmd.OpenForm method.
This is how my code looks like but it doesnt work.

DoCmd.OpenForm "Company Profile Form",acFormDS, ,"Between [Start] and [End] = Forms![Company Profile Form]![Transaction DAte]"


Any suggestions??

Thanks


MLT

 

[chrismcbride]

I have not had occasion to open a form within a specified date range, but I can tell you if you want to pass a Date in a text string you need to tell VBA what part of the string is the actual date. Here is an example of a call I make from a dialog box that takes a pair of dates that define a range for a report.

Private Sub cmdPrint_Click()
On Error GoTo EH

Dim stLinkCriteria As String
Dim stDocName As String

stLinkCriteria = "resolved_date Between #" & Me.start_date & "# AND #" & Me.end_date & "#"
stDocName = "rptClosedRequests"

DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
DoCmd.Close acForm, "dlgDateRange_rptClosedRequests"
DoCmd.Close acForm, "frmRequest"

SeeYa:
Exit Sub

EH:
MsgBox Err.Number & " - " & Err.description
Resume SeeYa

End Sub

The Number signs (#) must be within the double quotes that indicate a string and they tell access that what follows is a date.
Hope this helps.
Chris