Open Form

[neilmcmor]

I have a button on one form that when clicked is supposed to open another form. The form its supposed to open is based on a table with a composite key. Is it possible to get this bit of code to open the form based on 2 fields values? The wizard allows you one choice and as I know nothing abot VBA I need help.

Private Sub OpenBtn_Click()
On Error GoTo Err_OpenBtn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Attended_Classes_And_Students"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenBtn_Click:
Exit Sub

Err_OpenBtn_Click:
MsgBox Err.Description
Resume Exit_OpenBtn_Click

End Sub

 

[boblarson]

Change your DoCmd.OpenForm line to this:

DoCmd.OpenForm stDocName, , , "[YourField1NameHere]= " & Me!YourField1NameHere & " And [YourField2NameHere]= " & Me!YourField2NameHere

And if your items are text instead of numbers:

DoCmd.OpenForm stDocName, , , "[YourField1NameHere]='" & Me!YourField1NameHere & "' And [YourField2NameHere]= '" & Me!YourField2NameHere & "'"

 

[neilmcmor]

Dates

Thanks Bob. One of the fields is a date field, do I just use the text code for dates?

 

[boblarson]

Thanks Bob. One of the fields is a date field, do I just use the text code for dates?

No, you would need the date delimiter of #:

DoCmd.OpenForm stDocName, , , "[YourField1NameHere]= " & Me!YourField1NameHere & " And [YourDateFieldNameHere]=#" & Me!YourField2NameHere & "#"

 

[neilmcmor]

blank form

I have tried that but it keeps opening up blank forms. timetable_Code is a number.

Private Sub OpenBtn_Click()
On Error GoTo Err_OpenBtn_Click

Dim stDocName As String

stDocName = "Attended_Classes_And_Students"
DoCmd.OpenForm stDocName, , , "[Timetable_Code]= " & Me!Timetable_Code & " And [Date]=#" & Me!Date & "#"

Exit_OpenBtn_Click:
Exit Sub

Err_OpenBtn_Click:
MsgBox Err.Description
Resume Exit_OpenBtn_Click

End Sub

 

[boblarson]

For sure one thing you need is to change your field name so it isn't an Access reserved word (DATE is a reserved word).

 

[boblarson]

Also, your date field IS a date/time field and what format is it set at in the table? Is it shortdate or General, or what?

 

[neilmcmor]

Thanks for all your help Bob. I have changed all occurances of DATE throughout my database. And Open Form code work perfect.

 

[neilmcmor]

All my date fields are set to Short Date

 

[boblarson]

Thanks for all your help Bob. I have changed all occurances of DATE throughout my database. And Open Form code work perfect.

Good - yes, remember to watch for using Access reserved words in your object and field names. It will cause problems for you if you use them, so it's best to really be diligent in staying away from them.

Glad we could help.

 

[neilmcmor]

more date problems

The above code you gave me works fine bob. However it appears to only open the form correctly when the day part of the date is greater than 12. 13/11/07 it identifies as 13th November 2007. 12/11/07 it identifies as 11th December 2007. It is obviously getting mixed up with United States dates and UK dates. As I am in the UK I am storing the dates within the table as UK. Is there anyway i can ensure access uses UK dates all the time.

 

[boblarson]

You'll have to use US format in your Where clause because SQL wants it in US format.

DoCmd.OpenForm stDocName, , , "[Timetable_Code]= " & Me!Timetable_Code & " And [Date]=#" & Format(Me!Date, "mm/dd/yyyy") & "#"