A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Subform controls: ARGH!



 
 
Thread Tools Display Modes
  #11  
Old November 24th, 2006, 04:42 PM posted to microsoft.public.access.forms
scubadiver
external usenet poster
 
Posts: 1,673
Default Subform controls: ARGH!

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.


"Rick Brandt" wrote:

"scubadiver" wrote in message
...
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, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com




  #12  
Old November 24th, 2006, 04:57 PM posted to microsoft.public.access.forms
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Subform controls: ARGH!

"scubadiver" wrote in message
...
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, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


  #13  
Old November 24th, 2006, 05:17 PM posted to microsoft.public.access.forms
scubadiver
external usenet poster
 
Posts: 1,673
Default Subform controls: ARGH!

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.

"Rick Brandt" wrote:

"scubadiver" wrote in message
...
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, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



  #14  
Old November 24th, 2006, 05:26 PM posted to microsoft.public.access.forms
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Subform controls: ARGH!

"scubadiver" wrote in message
...
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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


  #15  
Old November 24th, 2006, 05:37 PM posted to microsoft.public.access.forms
scubadiver
external usenet poster
 
Posts: 1,673
Default Subform controls: ARGH!

Great. It isn't what I expected !!

Will sort it out on monday.

"Rick Brandt" wrote:

"scubadiver" wrote in message
...
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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



  #16  
Old November 27th, 2006, 10:06 AM posted to microsoft.public.access.forms
scubadiver
external usenet poster
 
Posts: 1,673
Default Subform controls: ARGH!

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" wrote:

"scubadiver" wrote in message
...
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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



  #17  
Old November 27th, 2006, 01:53 PM posted to microsoft.public.access.forms
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Subform controls: ARGH!


"scubadiver" wrote in message
...
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, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com





  #18  
Old November 27th, 2006, 02:45 PM posted to microsoft.public.access.forms
scubadiver
external usenet poster
 
Posts: 1,673
Default Subform controls: ARGH!

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.





"Rick Brandt" wrote:


"scubadiver" wrote in message
...
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, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com






  #19  
Old November 27th, 2006, 03:10 PM posted to microsoft.public.access.forms
Rick Brandt
external usenet poster
 
Posts: 4,354
Default Subform controls: ARGH!

scubadiver wrote:
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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.