Code Problem

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

The code below is causing a syntax error. It looks ok to
me, but VB doesnt like it. Can anyone tell me what I am
doing wrong? The idea here is to open a form
(frmStudyData) to the requested record (from frmUpdate1)
when input matches StudyName in the main table of the
frmStudyData.

DoCmd.OpenForm (frmStudyData, acFormView, StudyNumber =
Form!frmUpdate1!txtUpdate1, acFormEdit,,)

Any help would be greatly appreciated

Thanks
Bill
 
Try:
DoCmd.OpenForm "frmStudyData", acNormal,,"StudyNumber = " &
Form!frmUpdate1!txtUpdate1

Issues:
1. The form name is a string, and needs to go in brackets.

2. acFormView is not an option.

3. You need an extra comma before the WhereCondition.

4. You need to concatenate the value from the form into the WhereCondition.

Note: If StudyName is a Text type field, you need extra quotes, i.e.:
"StudyNumber = """ & Form!frmUpdate1!txtUpdate1 & """"
 
This syntax worked as far as opening the correct form,
however, it opens the form in add mode, not to the record
requested for updating
 
So, there is some reason why Access as not able to match the study number
with what was in the txtUpdate1. Could be content, delimiter, etc.
 
Allen, thanks again for the reply. I fear there is still
a syntax issue on this command. When the form frmUpdate1
opens requesting user input, the properties box also
appears leading me to believe there is still something
wrong in the script. I am including the entire script
below. Please let me know if you think I am still doing
something wrong

Option Compare Database

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

DoCmd.OpenForm "frmStudyData",
acNormal, , "StudyNumber" = Forms!frmUpdate1!txtUpdate1



Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub
 
bill said:
Allen, thanks again for the reply. I fear there is still
a syntax issue on this command. When the form frmUpdate1
opens requesting user input, the properties box also
appears leading me to believe there is still something
wrong in the script. I am including the entire script
below. Please let me know if you think I am still doing
something wrong

Option Compare Database

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

DoCmd.OpenForm "frmStudyData",
acNormal, , "StudyNumber" = Forms!frmUpdate1!txtUpdate1



Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub
Click to expand...

The code line you posted is not what Allen told you to use. Use this:

DoCmd.OpenForm "frmStudyData", acNormal, , _
"StudyNumber = " & Forms!frmUpdate1!txtUpdate1

The fact that the form's property sheet also appears (if I understand
what you're saying) only means that the property sheet was open when you
saved the form in design view, and you have the "Allow Design Changes"
property (on the Other tab of the property sheet, IIRC) set to "All
Views". I suggest you set that property to "Design View Only".
 
Thanks for picking up on this Dirk. I have adjusted the
code exactly as you have specified...

DoCmd.OpenForm "frmStudyData", acNormal, , "StudyNumber
= " & Forms!frmUpdate1!txtUpdate1

This is still not working.

As a summary, i have a form, frmUpdate1, whose control
source is the table Startup. On that form I have an
unbound text box called txtUpdate1. The form askes the
user to enter the study number they wish to update. I
would then like to have frmStartup open to the record
specified.

I have verified that the study number I am requesting is
in the table Startup.

I am a little new to Access, and I know this should not
be that complicated, and I apologize for the length of
this posting.

Any other ideas you might have, would be greatly
appeciated.

Thanks again
Bill
 
Actually Dirk, i now get an error message when I execute
the command button. What is it says is..

Syntax error (missing operator) in query
expression 'StudyNumber = 18360104'

18360104 is the study number i requested

Thanks again
 
Bill said:
Actually Dirk, i now get an error message when I execute
the command button. What is it says is..

Syntax error (missing operator) in query
expression 'StudyNumber = 18360104'

18360104 is the study number i requested
Click to expand...

That suggests to me that the field StudyNumber is a text field, not a
number field. If that's the case, try this version:

DoCmd.OpenForm "frmStudyData", acNormal, , _
"StudyNumber = '" & Forms!frmUpdate1!txtUpdate1 & "'"

Notice that I've included single-quote characters (') to surround the
number that is coming from the text box, thus indicating to Access that
this is a text value.
 
Back
Top