Subform controls: ARGH!

[scubadiver]

Why can't I ever remember the code?

I have a search facility with two unbound boxes on a form. One of the boxes
I want to search is in a subform. This is what I have:

[forms]![Queries Form]![AddressDet].[form]![address1]

Is this correct?

I know I have to distinguish between the name of the form and the "box" in
which it sits in the main form.

 

[Rick Brandt]

Why can't I ever remember the code?

I have a search facility with two unbound boxes on a form. One of the boxes
I want to search is in a subform. This is what I have:

[forms]![Queries Form]![AddressDet].[form]![address1]

Is this correct?

I know I have to distinguish between the name of the form and the "box" in
which it sits in the main form.

Looks correct. When in doubt use the builder. You get a nice GUI to navigate
to the control in question and in return get a reference that is guaranteed to
be correct.

 

[scubadiver]

I thought it was correct

I don't know whether you gathered the code is in VBA.

This is the code in its entirety as I have it now:



Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And
[forms]![queries form]![Branch form].[form]![address1] = '" & Me.address & "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Get the following message:

"The microsoft jet database engine does not recognise ....[ ]....as a valid
field name or expression".

????

Why can't I ever remember the code?

I have a search facility with two unbound boxes on a form. One of the boxes
I want to search is in a subform. This is what I have:

[forms]![Queries Form]![AddressDet].[form]![address1]

Is this correct?

I know I have to distinguish between the name of the form and the "box" in
which it sits in the main form.

Looks correct. When in doubt use the builder. You get a nice GUI to navigate
to the control in question and in return get a reference that is guaranteed to
be correct.

 

[Rick Brandt]

I thought it was correct

I don't know whether you gathered the code is in VBA.

This is the code in its entirety as I have it now:



Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And
[forms]![queries form]![Branch form].[form]![address1] = '" & Me.address & "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Get the following message:

"The microsoft jet database engine does not recognise ....[ ]....as a valid
field name or expression".

You should have the form reference outside the quotes...

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And " &
[forms]![queries form]![Branch form].[form]![address1] & " = '" & Me.address &
"'"

 

[scubadiver]

I assume there is a reason for the double quotes and ampersands around the
reference? Is it because the reference is in a subform?

[Forms]![Queries form]![branch form].Form![Address1]

I still have the above and the message is now: "Can't find the field 'branch
form' referred to in your expression". I still can't see what is going on!

I thought it was correct

I don't know whether you gathered the code is in VBA.

This is the code in its entirety as I have it now:



Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And
[forms]![queries form]![Branch form].[form]![address1] = '" & Me.address & "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Get the following message:

"The microsoft jet database engine does not recognise ....[ ]....as a valid
field name or expression".

You should have the form reference outside the quotes...

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And " &
[forms]![queries form]![Branch form].[form]![address1] & " = '" & Me.address &
"'"

 

[Douglas J. Steele]

The reason for the quotes and ampersands is that the reference to the
control has to be outside of the quotes so that you get its value. The
FindFirst method is unable to resolve the field reference on its own.

For the error you're getting, make sure that the name of the control on
Queries form that holds the subform is named branch form. Depending on how
you added branch form as a subform to Queries form, the name of the control
may not be the same as the name of the form being used as a subform: it's
the name of the control you want in that statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I assume there is a reason for the double quotes and ampersands around the
reference? Is it because the reference is in a subform?

[Forms]![Queries form]![branch form].Form![Address1]

I still have the above and the message is now: "Can't find the field
'branch
form' referred to in your expression". I still can't see what is going on!

I thought it was correct

I don't know whether you gathered the code is in VBA.

This is the code in its entirety as I have it now:



Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And
[forms]![queries form]![Branch form].[form]![address1] = '" &
Me.address & "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Get the following message:

"The microsoft jet database engine does not recognise ....[ ]....as a
valid
field name or expression".

You should have the form reference outside the quotes...

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And " &
[forms]![queries form]![Branch form].[form]![address1] & " = '" &
Me.address &
"'"

 

[scubadiver]

I am almost there but I get another error message: "run-time error "438".
Object doesn't support this property or method".

The reason for the quotes and ampersands is that the reference to the
control has to be outside of the quotes so that you get its value. The
FindFirst method is unable to resolve the field reference on its own.

For the error you're getting, make sure that the name of the control on
Queries form that holds the subform is named branch form. Depending on how
you added branch form as a subform to Queries form, the name of the control
may not be the same as the name of the form being used as a subform: it's
the name of the control you want in that statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I assume there is a reason for the double quotes and ampersands around the
reference? Is it because the reference is in a subform?

[Forms]![Queries form]![branch form].Form![Address1]

I still have the above and the message is now: "Can't find the field
'branch
form' referred to in your expression". I still can't see what is going on!

I thought it was correct

I don't know whether you gathered the code is in VBA.

This is the code in its entirety as I have it now:



Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And
[forms]![queries form]![Branch form].[form]![address1] = '" &
Me.address & "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Get the following message:

"The microsoft jet database engine does not recognise ....[ ]....as a
valid
field name or expression".

You should have the form reference outside the quotes...

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And " &
[forms]![queries form]![Branch form].[form]![address1] & " = '" &
Me.address &
"'"

 

[scubadiver]

I am almost there!

The reference seems to work now because I also have the following error:

"The microsoft jet database engine does not recognise 'Add1e' as a valid
field name or expression".

add1e is a dummy address record in [address1]

The reason for the quotes and ampersands is that the reference to the
control has to be outside of the quotes so that you get its value. The
FindFirst method is unable to resolve the field reference on its own.

For the error you're getting, make sure that the name of the control on
Queries form that holds the subform is named branch form. Depending on how
you added branch form as a subform to Queries form, the name of the control
may not be the same as the name of the form being used as a subform: it's
the name of the control you want in that statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I assume there is a reason for the double quotes and ampersands around the
reference? Is it because the reference is in a subform?

[Forms]![Queries form]![branch form].Form![Address1]

I still have the above and the message is now: "Can't find the field
'branch
form' referred to in your expression". I still can't see what is going on!

I thought it was correct

I don't know whether you gathered the code is in VBA.

This is the code in its entirety as I have it now:



Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And
[forms]![queries form]![Branch form].[form]![address1] = '" &
Me.address & "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Get the following message:

"The microsoft jet database engine does not recognise ....[ ]....as a
valid
field name or expression".

You should have the form reference outside the quotes...

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And " &
[forms]![queries form]![Branch form].[form]![address1] & " = '" &
Me.address &
"'"

 

[scubadiver]

If I open the form and do a search I get a "missing operator" error so I
think these two errors are related?

I am almost there!

The reference seems to work now because I also have the following error:

"The microsoft jet database engine does not recognise 'Add1e' as a valid
field name or expression".

add1e is a dummy address record in [address1]

The reason for the quotes and ampersands is that the reference to the
control has to be outside of the quotes so that you get its value. The
FindFirst method is unable to resolve the field reference on its own.

For the error you're getting, make sure that the name of the control on
Queries form that holds the subform is named branch form. Depending on how
you added branch form as a subform to Queries form, the name of the control
may not be the same as the name of the form being used as a subform: it's
the name of the control you want in that statement.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I assume there is a reason for the double quotes and ampersands around the
reference? Is it because the reference is in a subform?

[Forms]![Queries form]![branch form].Form![Address1]

I still have the above and the message is now: "Can't find the field
'branch
form' referred to in your expression". I still can't see what is going on!



:

I thought it was correct

I don't know whether you gathered the code is in VBA.

This is the code in its entirety as I have it now:



Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And
[forms]![queries form]![Branch form].[form]![address1] = '" &
Me.address & "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Get the following message:

"The microsoft jet database engine does not recognise ....[ ]....as a
valid
field name or expression".

You should have the form reference outside the quotes...

rstClone.FindFirst "[CustomerName] = '" & Me.Customer & "' And " &
[forms]![queries form]![Branch form].[form]![address1] & " = '" &
Me.address &
"'"

 

[Rick Brandt]

The reference seems to work now because I also have the following error:

"The microsoft jet database engine does not recognise 'Add1e' as a valid
field name or expression".

add1e is a dummy address record in [address1]

When you get an error that indicates data is being treated as a field name then
that means you are missing text delimiters. For example, consider the following
two filter phrases...

SomeField = BlahBlahBlah

SomeField = 'BlahBlahBlah'

The first will be interpreted as "filter on records where the field SomeField is
equal to the field BlahBlahBlah". The second will be interpreted as "filter on
records where the field SomeField contains the string 'BlahBlahBlah'".

Your error suggests that within your filter string Add1e does not have quotes
around it when it should have.

 

[scubadiver]

Here is the code as it is now.....

Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName]= '" & Me.Customer & "' And " &
[Forms]![queries form]![Branch form].[Form]![Address1] & " = '" & Me.address
& "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

If I get rid of the quote and ampersand from around the second reference

" & [Forms]![queries form]![Branch form].[Form]![Address1] & "

Access doesn't recognise it.

The reference seems to work now because I also have the following error:

"The microsoft jet database engine does not recognise 'Add1e' as a valid
field name or expression".

add1e is a dummy address record in [address1]

When you get an error that indicates data is being treated as a field name then
that means you are missing text delimiters. For example, consider the following
two filter phrases...

SomeField = BlahBlahBlah

SomeField = 'BlahBlahBlah'

The first will be interpreted as "filter on records where the field SomeField is
equal to the field BlahBlahBlah". The second will be interpreted as "filter on
records where the field SomeField contains the string 'BlahBlahBlah'".

Your error suggests that within your filter string Add1e does not have quotes
around it when it should have.

 

[Rick Brandt]

Here is the code as it is now.....

Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName]= '" & Me.Customer & "' And " &
[Forms]![queries form]![Branch form].[Form]![Address1] & " = '" & Me.address
& "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Add this line right after the Set rstClone line...

Debug.print "[CustomerName]= '" & Me.Customer & "' And " & [Forms]![queries
form]![Branch Form].[Form]![Address1] & " = '" & Me.address & "'"

....then check the debug window after running the code. Does the string sent to
the debug window look correct?

 

[scubadiver]

I am not entirely sure what you mean by "debug" window. Is that the VBA code
window?

I still get a "missing operator" error. Nothing seems to have changed.

Here is the code as it is now.....

Dim rstClone As Object

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName]= '" & Me.Customer & "' And " &
[Forms]![queries form]![Branch form].[Form]![Address1] & " = '" & Me.address
& "'"

Me.Bookmark = rstClone.Bookmark

Set rstClone = Nothing

End Sub

Add this line right after the Set rstClone line...

Debug.print "[CustomerName]= '" & Me.Customer & "' And " & [Forms]![queries
form]![Branch Form].[Form]![Address1] & " = '" & Me.address & "'"

....then check the debug window after running the code. Does the string sent to
the debug window look correct?

 

[Rick Brandt]

I am not entirely sure what you mean by "debug" window. Is that the VBA code
window?

If you press Ctl-G the VBA editor window opens and one "pane" of that is the
immediate/debug window. That is where the Debug.Print command will place the
evaluated output of your string.

 

[scubadiver]

Great. It isn't what I expected !!

Will sort it out on monday.

 

[scubadiver]

I have tried it when i first opened the form and this is what I get:

[CustomerName]= 'Joe Bloggs' And 5 cedar House = 'Add1c'

I *can* understand why this has happened.

To explain, my earlier version of the db was not properly designed. Each
customer can have more than one address (like a bank has more than one
branch) but I originally decided not to have a 1:m relationship so the
contact information would be in the main for with the customer name.

A part of the code I used (which worked perfectly ok) was the following:

rstClone.FindFirst "[CustomerName] = '" & Me.Cname & "' And [Address1] =
'" & Me.AddrSrch & "'"


The difference is that there are no quotes or ampersands around [Address1].
The ampersands picks up the information within the field rather than the name
of the field itself hence why I am getting

5 cedar House = 'Add1c'

instead of

[address1] = "5 Cedar House"

This suggests to me that I shouldn't have the ampersands in the current code

" & [Forms]![queries form]![Branch Form].[Form]![Address1] & "


If I remove them and re-run it, Access says it is an invalid field name or
expression yet when i look at the evaluation I get:

[CustomerName]= 'Joe Bloggs' And [Forms]![queries form]![Branch
Form].[Form]![Address1] = 'Add1c'

that is what I want.

I am wondering whether there is something to do with having a 1:m
relationship (or a form and subform) that is stopping the code from running
properly.

 

[Rick Brandt]

I have tried it when i first opened the form and this is what I get:

[CustomerName]= 'Joe Bloggs' And 5 cedar House = 'Add1c'

I *can* understand why this has happened.

To explain, my earlier version of the db was not properly designed. Each
customer can have more than one address (like a bank has more than one
branch) but I originally decided not to have a 1:m relationship so the
contact information would be in the main for with the customer name.

A part of the code I used (which worked perfectly ok) was the following:

rstClone.FindFirst "[CustomerName] = '" & Me.Cname & "' And [Address1] =
'" & Me.AddrSrch & "'"


The difference is that there are no quotes or ampersands around [Address1].
The ampersands picks up the information within the field rather than the name
of the field itself hence why I am getting

5 cedar House = 'Add1c'

instead of

[address1] = "5 Cedar House"

This suggests to me that I shouldn't have the ampersands in the current code

" & [Forms]![queries form]![Branch Form].[Form]![Address1] & "


If I remove them and re-run it, Access says it is an invalid field name or
expression yet when i look at the evaluation I get:

[CustomerName]= 'Joe Bloggs' And [Forms]![queries form]![Branch
Form].[Form]![Address1] = 'Add1c'

that is what I want.

Actually no, it is not. You want the evaluated expression to contain the value
found at the form reference, not the reference itself. You need the evaluated
expression to be...

[CustomerName]= 'Joe Bloggs' And '5 cedar House' = Add1c

That's assuming that Add1C is a field name. This has been a funny looking
expression from the beginning. Even though the result is the same just about
anyone else would have written it this way...

[CustomerName]= 'Joe Bloggs' And [Add1c] = '5 cedar House'

....with the form expression on the right side of the second = instead of on the
left.

 

[scubadiver]

You have completely misunderstood. Add1c is just dummy data for me to work
with.

[Customer] and [address] are the fields I am using to do the search.
[CustomerName] and [address1] are the fields I am searching.

I explained it in my second response (11/23) when I copied the entire code.

Do you understand what I am trying to achieve?

So if I select "Joe Bloggs" and his address, "5 Cedar House", I want to
press the button and find that particular record. As I said, the code I used
for an earlier version worked perfectly fine, but now [address1] is now in a
sub form, NOT on the main form. That is the only difference so I can't see
why the code won't work now.

I have tried it when i first opened the form and this is what I get:

[CustomerName]= 'Joe Bloggs' And 5 cedar House = 'Add1c'

I *can* understand why this has happened.

To explain, my earlier version of the db was not properly designed. Each
customer can have more than one address (like a bank has more than one
branch) but I originally decided not to have a 1:m relationship so the
contact information would be in the main for with the customer name.

A part of the code I used (which worked perfectly ok) was the following:

rstClone.FindFirst "[CustomerName] = '" & Me.Cname & "' And [Address1] =
'" & Me.AddrSrch & "'"


The difference is that there are no quotes or ampersands around [Address1].
The ampersands picks up the information within the field rather than the name
of the field itself hence why I am getting

5 cedar House = 'Add1c'

instead of

[address1] = "5 Cedar House"

This suggests to me that I shouldn't have the ampersands in the current code

" & [Forms]![queries form]![Branch Form].[Form]![Address1] & "


If I remove them and re-run it, Access says it is an invalid field name or
expression yet when i look at the evaluation I get:

[CustomerName]= 'Joe Bloggs' And [Forms]![queries form]![Branch
Form].[Form]![Address1] = 'Add1c'

that is what I want.

Actually no, it is not. You want the evaluated expression to contain the value
found at the form reference, not the reference itself. You need the evaluated
expression to be...

[CustomerName]= 'Joe Bloggs' And '5 cedar House' = Add1c

That's assuming that Add1C is a field name. This has been a funny looking
expression from the beginning. Even though the result is the same just about
anyone else would have written it this way...

[CustomerName]= 'Joe Bloggs' And [Add1c] = '5 cedar House'

....with the form expression on the right side of the second = instead of on the
left.

 

[Rick Brandt]

You have completely misunderstood. Add1c is just dummy data for me to
work with.

[Customer] and [address] are the fields I am using to do the search.
[CustomerName] and [address1] are the fields I am searching.

I explained it in my second response (11/23) when I copied the entire
code.

Do you understand what I am trying to achieve?

So if I select "Joe Bloggs" and his address, "5 Cedar House", I want
to press the button and find that particular record. As I said, the
code I used for an earlier version worked perfectly fine, but now
[address1] is now in a sub form, NOT on the main form. That is the
only difference so I can't see why the code won't work now.

If you have [CustomerName] in the main form and [address1] in a subform then
a single filter CANNOT find this record. A filter can only act on either
your main form OR your subform. You would have to apply a filter first on
the main form that only filters on [CustomerName]. If you have the main
form and subform linked properly then the subform will automatically then
display just the addresses that match the record in the main form. Then you
would have to apply a spearate filter to the subform to find the specific
address you want.