Syntax error 3075 missing operator

[Skunk]

Hi all!

I get "error 3075 Syntax error (missing operator) in query expression
'[InvNum]81441." (81441 is the invoice number being acted upon.)

DoCmd.OpenForm "2frmDELabor", , , "[InvNum]" & Me![InvNum], acFormEdit

1. InvNum is a number field, long integer.
2. The command is attached to a button on [2frmDEInvoice].
3. [2frmDELabor] is a parent form to [2frmDELaborRP1].
4. [2frmDELaborRP1] is named LaborRP1
5. LaborRP1 is linked to parent via [InvNum].

Is there something about the subform? Am I not calling it correctly?

I would gratefully accept any and all aid.

Thanks,
Skunk

 

[Dirk Goldgar]

Hi all!

I get "error 3075 Syntax error (missing operator) in query expression
'[InvNum]81441." (81441 is the invoice number being acted upon.)

DoCmd.OpenForm "2frmDELabor", , , "[InvNum]" & Me![InvNum], acFormEdit

1. InvNum is a number field, long integer.
2. The command is attached to a button on [2frmDEInvoice].
3. [2frmDELabor] is a parent form to [2frmDELaborRP1].
4. [2frmDELaborRP1] is named LaborRP1
5. LaborRP1 is linked to parent via [InvNum].

Is there something about the subform? Am I not calling it correctly?

I would gratefully accept any and all aid.

This argument:

"[InvNum]" & Me![InvNum]

.... needs to be:

"[InvNum] = " & Me![InvNum]

 

[Skunk]

Thankyou Dirk. The form opens now but without [InvNum]...its control is
empty in both places -- on the parent (unbound) and the child (bound).

I thought certainly the missing "=" did it. Did I get it wrong?

DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum] , acFormEdit"


Thanks for helping me.
Skunk

Hi all!

I get "error 3075 Syntax error (missing operator) in query expression
'[InvNum]81441." (81441 is the invoice number being acted upon.)

DoCmd.OpenForm "2frmDELabor", , , "[InvNum]" & Me![InvNum], acFormEdit

1. InvNum is a number field, long integer.
2. The command is attached to a button on [2frmDEInvoice].
3. [2frmDELabor] is a parent form to [2frmDELaborRP1].
4. [2frmDELaborRP1] is named LaborRP1
5. LaborRP1 is linked to parent via [InvNum].

Is there something about the subform? Am I not calling it correctly?

I would gratefully accept any and all aid.

This argument:

"[InvNum]" & Me![InvNum]

... needs to be:

"[InvNum] = " & Me![InvNum]

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thankyou Dirk. The form opens now but without [InvNum]...its control is
empty in both places -- on the parent (unbound) and the child (bound).

I thought certainly the missing "=" did it. Did I get it wrong?

DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum] , acFormEdit"

The parent form "2frmDELabor" is unbound? Then the where-condition argument
is irrelevant, since that just filters the form to display existings records
from the form's recordsource that meet the criteria -- and in this case, the
form has no recordsource (and no existing records).

If [InvNum] is the name of an unbound control on the unbound form
"2frmDELabor", try this instead:

DoCmd.OpenForm "2frmDELabor"
Forms![2frmDELabor]!InvNum = Me!InvNum

 

[Skunk]

No working yet.

To clarify:

1. InvNum is a number field, long integer.

2. The do command calling the labor form parent/child is attached to a
button on [2frmDEInvoice]. This form is bound to qry2Job.

3. [2frmDELabor] is a parent form to [2frmDELaborRP1]. The parent form is
bound to qry2JobLaborRP.

4. [2frmDELaborRP1] is named LaborRP1. The child form is bound to
tbl3RP1Labor.

5. LaborRP1 is linked to parent via [InvNum].

Resident on [2frmDELabor] is an unbound control to show the user [InvNum].

Does that tell you anything? I hope!

Skunk.

Thankyou Dirk. The form opens now but without [InvNum]...its control is
empty in both places -- on the parent (unbound) and the child (bound).

I thought certainly the missing "=" did it. Did I get it wrong?

DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum] , acFormEdit"

The parent form "2frmDELabor" is unbound? Then the where-condition argument
is irrelevant, since that just filters the form to display existings records
from the form's recordsource that meet the criteria -- and in this case, the
form has no recordsource (and no existing records).

If [InvNum] is the name of an unbound control on the unbound form
"2frmDELabor", try this instead:

DoCmd.OpenForm "2frmDELabor"
Forms![2frmDELabor]!InvNum = Me!InvNum

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

No working yet.

To clarify:

1. InvNum is a number field, long integer.

2. The do command calling the labor form parent/child is attached to a
button on [2frmDEInvoice]. This form is bound to qry2Job.

3. [2frmDELabor] is a parent form to [2frmDELaborRP1]. The parent form is
bound to qry2JobLaborRP.

4. [2frmDELaborRP1] is named LaborRP1. The child form is bound to
tbl3RP1Labor.

You mean that the subform control (on the parent form [2frmDELabor]) is
named "LaborRP1"?

5. LaborRP1 is linked to parent via [InvNum].

Resident on [2frmDELabor] is an unbound control to show the user [InvNum].

Does that tell you anything? I hope!

It tells me something, but not quite enough. What are:

1. The fields returned by qry2JobLabor?

2. The Link Master Fields and Link Child Fields properties of the subform
LaborRP1?

 

[Skunk]

1. There are many fields from several tables. [InvNum] in the first field in
my query from my main job table and the other tables are linked to it via
[InvNum] with RIntegrity enforced, updates,deletes.

2. [InvNum] and [InvNum]

I appreciate your spending this time.
Skunk

No working yet.

To clarify:

1. InvNum is a number field, long integer.

2. The do command calling the labor form parent/child is attached to a
button on [2frmDEInvoice]. This form is bound to qry2Job.

3. [2frmDELabor] is a parent form to [2frmDELaborRP1]. The parent form is
bound to qry2JobLaborRP.

4. [2frmDELaborRP1] is named LaborRP1. The child form is bound to
tbl3RP1Labor.

You mean that the subform control (on the parent form [2frmDELabor]) is
named "LaborRP1"?

5. LaborRP1 is linked to parent via [InvNum].

Resident on [2frmDELabor] is an unbound control to show the user [InvNum].

Does that tell you anything? I hope!

It tells me something, but not quite enough. What are:

1. The fields returned by qry2JobLabor?

2. The Link Master Fields and Link Child Fields properties of the subform
LaborRP1?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

1. There are many fields from several tables. [InvNum] in the first field
in
my query from my main job table and the other tables are linked to it via
[InvNum] with RIntegrity enforced, updates,deletes.

2. [InvNum] and [InvNum]

I'm a bit confused, then. You said:

Resident on [2frmDELabor] is an unbound control to show the user
[InvNum].

If, as I understand it, qry2JobLabor is the recordsource of form
[2frmDELabor], and qry2JobLabor includes the field InvNum in its selected
fields, then why do you have an *unbound* control on [2frmDELabor] to
display InvNum? What is the name of that unbound control? Is there a
control that *is* bound to InvNum?

If [2frmDELabor] is a bound form after all, as now seems to be the case, it
seem like we were on the right track before, with the where-condition:

DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum]

So if that didn't work, something's screwy.

There's something about your setup that I'm not understanding. Along with
posting answers to the questions above, could you please explain the bigger
picture of what you're trying to do with this form?

 

[Skunk]

1. There are many fields from several tables. [InvNum] in the first field
in
my query from my main job table and the other tables are linked to it via
[InvNum] with RIntegrity enforced, updates,deletes.

2. [InvNum] and [InvNum]

I'm a bit confused, then. You said:

Resident on [2frmDELabor] is an unbound control to show the user
[InvNum].

If, as I understand it, qry2JobLabor is the recordsource of form
[2frmDELabor], and qry2JobLabor includes the field InvNum in its selected
fields, then why do you have an *unbound* control on [2frmDELabor] to
display InvNum? What is the name of that unbound control? Is there a
control that *is* bound to InvNum?

If [2frmDELabor] is a bound form after all, as now seems to be the case, it
seem like we were on the right track before, with the where-condition:

DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum]

So if that didn't work, something's screwy.

There's something about your setup that I'm not understanding. Along with
posting answers to the questions above, could you please explain the bigger
picture of what you're trying to do with this form?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Well, there is no doubt something screwy, but what?!?!

While the Invoice form is open, user clicks a button to add labor record,
calling the labor parent/child form, with this code:

'Adds and opens labor details record
DoCmd.OpenForm "2frmDELabor", , , , acFormAdd
Forms![2frmDELabor]![LaborCG1frm].Form.InvNum = Me.InvNum

The Labor parent/child form opens to add the record, user inputs details.
User closes the Labor parent/child form. This does ok -- except the InvNum
(bound) display control on the parent (for the user's viewing) is empty. All
edits are done on the child form, [LaborRP1].

At times user may wish to go right back to the labor form, so...code:
'Open labor details record for edit
DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum] , acFormEdit"

[2frmDELabor] is bound to qry2JobLabor. (Parent) Contains [InvNum].
[LaborRP1] is bound to [tbl 3 LaborRP1]. (Child) Contains [InvNum]
The parent/child forms are linked via [InvNum]

[2frmDELabor] and [LaborRP1] are yes to DataEntry, Delete, Edit, Add. (I
think this is superceded by the DoCmd above. True?)

I think that about says it all, but you are the expert. Does this help?

Thanks,
Skunk

 

[Skunk]

Oh, and I get an error 2501, "open form action cancelled", followed by "must
enter value in the [tbl 3 LaborRP1].InvNum field when I try acFormEdit.

Skunk

1. There are many fields from several tables. [InvNum] in the first field
in
my query from my main job table and the other tables are linked to it via
[InvNum] with RIntegrity enforced, updates,deletes.

2. [InvNum] and [InvNum]

I'm a bit confused, then. You said:

Resident on [2frmDELabor] is an unbound control to show the user
[InvNum].

If, as I understand it, qry2JobLabor is the recordsource of form
[2frmDELabor], and qry2JobLabor includes the field InvNum in its selected
fields, then why do you have an *unbound* control on [2frmDELabor] to
display InvNum? What is the name of that unbound control? Is there a
control that *is* bound to InvNum?

If [2frmDELabor] is a bound form after all, as now seems to be the case, it
seem like we were on the right track before, with the where-condition:

DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum]

So if that didn't work, something's screwy.

There's something about your setup that I'm not understanding. Along with
posting answers to the questions above, could you please explain the bigger
picture of what you're trying to do with this form?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Skunk]

Correction: Forms![2frmDELabor]![LaborCG1frm].Form.InvNum = Me.InvNum
should be changed to ...[LaborRP1]...

Oh, and I get an error 2501, "open form action cancelled", followed by "must
enter value in the [tbl 3 LaborRP1].InvNum field when I try acFormEdit.

Skunk

1. There are many fields from several tables. [InvNum] in the first field
in
my query from my main job table and the other tables are linked to it via
[InvNum] with RIntegrity enforced, updates,deletes.

2. [InvNum] and [InvNum]

I'm a bit confused, then. You said:

Resident on [2frmDELabor] is an unbound control to show the user
[InvNum].

If, as I understand it, qry2JobLabor is the recordsource of form
[2frmDELabor], and qry2JobLabor includes the field InvNum in its selected
fields, then why do you have an *unbound* control on [2frmDELabor] to
display InvNum? What is the name of that unbound control? Is there a
control that *is* bound to InvNum?

If [2frmDELabor] is a bound form after all, as now seems to be the case, it
seem like we were on the right track before, with the where-condition:

DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum]

So if that didn't work, something's screwy.

There's something about your setup that I'm not understanding. Along with
posting answers to the questions above, could you please explain the bigger
picture of what you're trying to do with this form?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Skunk]

Update: I tested opening the child form as a stand-alone. It opens and
edits ok.

So, the issue is the Parent form? When I call it to edit -- after
successfully adding, the invoice number is null. It is not pulling the
record.

What can cause this?

1. There are many fields from several tables. [InvNum] in the first field
in
my query from my main job table and the other tables are linked to it via
[InvNum] with RIntegrity enforced, updates,deletes.

2. [InvNum] and [InvNum]

I'm a bit confused, then. You said:

Resident on [2frmDELabor] is an unbound control to show the user
[InvNum].

If, as I understand it, qry2JobLabor is the recordsource of form
[2frmDELabor], and qry2JobLabor includes the field InvNum in its selected
fields, then why do you have an *unbound* control on [2frmDELabor] to
display InvNum? What is the name of that unbound control? Is there a
control that *is* bound to InvNum?

If [2frmDELabor] is a bound form after all, as now seems to be the case, it
seem like we were on the right track before, with the where-condition:

DoCmd.OpenForm "2frmDELabor", , , [InvNum] = " & Me![InvNum]

So if that didn't work, something's screwy.

There's something about your setup that I'm not understanding. Along with
posting answers to the questions above, could you please explain the bigger
picture of what you're trying to do with this form?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

While the Invoice form is open, user clicks a button to add labor record,
calling the labor parent/child form, with this code:

'Adds and opens labor details record
DoCmd.OpenForm "2frmDELabor", , , , acFormAdd
Forms![2frmDELabor]![LaborCG1frm].Form.InvNum = Me.InvNum

Should that be "LaborRP1" instead of "LaborCG1frm"?

In any case, this looks wrong. You're opening a main form, and then setting
a link-field value on a subform. It seems to me that you should probably be
doing this:

DoCmd.OpenForm "2frmDELabor", , , , acFormAdd
Forms![2frmDELabor]!InvNum = Me.InvNum

Then, assuming that the subform is linked to the main form by the InvNum
field, any details entered on the subform will automatically pick up the
value of InvNum from the parent form.

If you do that, then opening the parent form again later with this:

DoCmd.OpenForm "2frmDELabor", _
WhereCondition:="InvNum = " & Me!InvNum

or, if InvNum is a text field, this:

DoCmd.OpenForm "2frmDELabor", _
WhereCondition:="InvNum = '" & Me!InvNum & "'"

should automatically show the correct information.