Old Access to Access 2007 Criteria issue

The code is:

Private Sub cmdBuild_Click()
On Error GoTo Err_cmdBuild_Click

Shift = InputBox("Enter shift")

If Shift = "D" Or Shift = "S" Or Shift = "N" Then
If Shift = "N" Then
Shift = Shift & "*"
Else
Shift = Shift
End If
Else
Shift = "*"
End If

txtShift.SetFocus
txtShift.Text = Shift


pdate = InputBox("enter Production Start Date")
pdate = Format(pdate, shortdate)
pdate1 = Format(DateAdd("d", 1, pdate), shortdate)

If IsDate(pdate) Then
pdate = pdate
pdateyy = Format(pdate, "yyyymmdd")
pdate1 = pdate1
pdate1yy = Format(pdate, "yyyymmdd")
Else
pdate = Format(DateAdd("d", -1, Now()), shortdate)
pdate1 = Format(DateAdd("d", 1, Now()), shortdate)
pdateyy = Format(pdate, "yyyymmdd")
pdate1yy = Format(pdate, "yyyymmdd")
End If

txtStart.SetFocus
txtStart.Text = pdate

pdate2 = InputBox("enter Production Stop Date")
pdate2 = Format(pdate2, shortdate)
pdate3 = Format(DateAdd("d", 1, pdate2), shortdate)

If IsDate(pdate2) Then
pdate2 = pdate2
pdate3 = pdate3
pdate2yy = Format(pdate2, "yyyymmdd")
pdate3yy = Format(pdate3, "yyyymmdd")
Else
pdate2 = Format(DateAdd("d", -1, Now()), shortdate)
pdate3 = Format(DateAdd("d", 1, Now()), shortdate)
pdate2yy = Format(pdate2, "yyyymmdd")
pdate3yy = Format(pdate3, "yyyymmdd")
End If

txtstop.SetFocus
txtstop.Text = pdate2

order = "00000"

'Exit Sub
If MsgBox("Do you want to refresh the audit files? Do this first time you run for the day", vbYesNo) = vbYes Then
DoCmd.SetWarnings (warningsoff)
DoCmd.OpenQuery "xxxxxxxxxx", acViewNormal, acEdit
DoCmd.OpenQuery "xxxxxxxxxx", acViewNormal, acEdit
DoCmd.OpenQuery "xxxxxxxxxx", acViewNormal, acEdit
DoCmd.OpenQuery "xxxxxxxxxx", acViewNormal, acEdit
DoCmd.OpenQuery "xxxxxxxxxx", acViewNormal, acEdit
DoCmd.OpenQuery "xxxxxxxxxx", acViewNormal, acEdit

DoCmd.SetWarnings (WarningsOn)
MsgBox ("Files Updated Run Reports now")
End If
Exit_cmdBuild_Click:
Exit Sub

Err_cmdBuild_Click:
MsgBox Err.Description
Resume Exit_cmdBuild_Click

End Sub

After [MsgBox("Do you want to refresh the audit files? Do this first time you run for the day", vbYesNo) = vbYes] I get the enter parameter box.

In the criteria of one query I am using :

Like [forms]![main].[shift]

and this is the first parameter not recognized (calling up the enter parameter box).

The form works fine in my old Access database but not 2007???

Does it tell you the name of the parameter it's looking for?

By the way I don't think this is a version issue - I've not heard of any major changes, apart from the interface, in Access 2007.

It is asking for Forms!main.shift and then Forms!main.pdate and then Forms!main.order.

I think it could be just a syntax error but I don't know why it would function properly in the old database.

Silly question, but is the form actually open when you run this code.O

Yes the code is started by clicking on a command button.

OK.
I resolved the "enter parameter value" box issue:

>=[forms]![main].[pdate]
needs to be
>=[forms]![main]![pdate]

Now my queries are not recognizing the value of pdate. The query works if I put :

>=[forms]![main]![txtStart]

Where txtStart is the name of the text box that holds pdate. But, I need the queries to reference all pdate, pdate1, pdate2, and pdate3. (pdate2 is the value in txtStop)

I have jumped over from this thread: http://www.mrexcel.com/forum/showthread.php?p=1607798

You should really stick to the one thread......

I think I can see the problem. It looks like pdate & pdate2 etc are values in the VBA code but you are trying to use them as if they were text boxes on the form. In addition, I cannot see where you have taken the values of pdate etc and pushed them back into the text boxes on the form. I think that is the disconnect.

In the other thread you mentioned about the +1 and -1 days - that is counter-intuitive to use different criteria to that actually entered and I would be asking 'why?' of either the developer or user.

Another issue I can see is that none of the variables have been declared (assuming that is the full code). Have a look at 'Option Explicit' - it forces you to declare your variables (e.g. by using 'Dim' statements) and can help prevent errors by using the incorrect variable etc.

Like I mentioned in the other thread, I still think it would be easier to capture the user input directly on the form, they click the button and the first thing the code does is make sure every mandatory text box has been completed.

Rather than convert the VBA code (2007 can be picky) now might be a good time to understand the old code and then re-write it your own way in 2007 so that you have a full understanding of what is going on. I also mentioned in the other thread about having the queries pick up the criteria directly from the form, rather than using VBA - insofar as I can see you won't be losing any functionality and IMO you will find the technique is simpler.

Andrew

Thank you. I have not been able to work on this any further but I will take your advice and see what I can do. I may be back for more help though. (I will keep it in this thread too)