Code will work on one table and form but not on another?

I have some code that opens a form. If the code is based on one table and
form it works fine. If I try to run the same code against another table with
only the table and destination form names changed the form opens but doesn't
show any records. Could someone help me where to start looking please. Below
(for those who want to read it!) is the two sets of code:

Thanks for anyone who can help, I was hoping to get this to bed befiore
Christmas :-(
Tony

This DOESN'T work:

Private Sub Command35_Click()
On Error GoTo Err_Command35_Click
    Dim strsql As String
    Dim strtxtcompany As String
    Dim dattxtdate As Date
    
    dattxtdate = Me.txtmontha
    strtxtcompany = Me.cmbselectcompany
    strsql = "SELECT * FROM [tblmainIrish] " & _
    "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
    "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
    "AND [txtcompany] = '" & strtxtcompany & "'"
    Forms!frmMain!SubForm1.SourceObject = "subformIrish"

Forms!frmMain!SubForm1.Form.RecordSource = strsql

Debug.Print strsql


Exit_Command35_Click:
    Exit Sub

Err_Command35_Click:
    MsgBox Err.Description
    Resume Exit_Command35_Click
    
End Sub

This DOESN'T work:

Private Sub Command35_Click()
On Error GoTo Err_Command35_Click
    Dim strsql As String
    Dim strtxtcompany As String
    Dim dattxtdate As Date
    
    dattxtdate = Me.txtmontha
    strtxtcompany = Me.cmbselectcompany
    strsql = "SELECT * FROM [tblmaintabs] " & _
    "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
    "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
    "AND [txtcompany] = '" & strtxtcompany & "'"
    Forms!frmMain!SubForm1.SourceObject = "subformFDA"

Forms!frmMain!SubForm1.Form.RecordSource = strsql

Debug.Print strsql


Exit_Command35_Click:
    Exit Sub

Err_Command35_Click:
    MsgBox Err.Description
    Resume Exit_Command35_Click
    
End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
TonyWilliams
12/23/2009 5:12:39 PM
access.formscoding 7493 articles. 0 followers. Follow

19 Replies
981 Views

Similar Articles

[PageSpeed] 36

Tony

What happens if you use the SQL statement that's supposed to fill the second 
form in a query instead?  Does it return any records?

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"TonyWilliams via AccessMonster.com" <u56994@uwe> wrote in message 
news:a106d55ba7032@uwe...
>I have some code that opens a form. If the code is based on one table and
> form it works fine. If I try to run the same code against another table 
> with
> only the table and destination form names changed the form opens but 
> doesn't
> show any records. Could someone help me where to start looking please. 
> Below
> (for those who want to read it!) is the two sets of code:
>
> Thanks for anyone who can help, I was hoping to get this to bed befiore
> Christmas :-(
> Tony
>
> This DOESN'T work:
>
> Private Sub Command35_Click()
> On Error GoTo Err_Command35_Click
>    Dim strsql As String
>    Dim strtxtcompany As String
>    Dim dattxtdate As Date
>
>    dattxtdate = Me.txtmontha
>    strtxtcompany = Me.cmbselectcompany
>    strsql = "SELECT * FROM [tblmainIrish] " & _
>    "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>    "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>    "AND [txtcompany] = '" & strtxtcompany & "'"
>    Forms!frmMain!SubForm1.SourceObject = "subformIrish"
>
> Forms!frmMain!SubForm1.Form.RecordSource = strsql
>
> Debug.Print strsql
>
>
> Exit_Command35_Click:
>    Exit Sub
>
> Err_Command35_Click:
>    MsgBox Err.Description
>    Resume Exit_Command35_Click
>
> End Sub
>
> This DOESN'T work:
>
> Private Sub Command35_Click()
> On Error GoTo Err_Command35_Click
>    Dim strsql As String
>    Dim strtxtcompany As String
>    Dim dattxtdate As Date
>
>    dattxtdate = Me.txtmontha
>    strtxtcompany = Me.cmbselectcompany
>    strsql = "SELECT * FROM [tblmaintabs] " & _
>    "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>    "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>    "AND [txtcompany] = '" & strtxtcompany & "'"
>    Forms!frmMain!SubForm1.SourceObject = "subformFDA"
>
> Forms!frmMain!SubForm1.Form.RecordSource = strsql
>
> Debug.Print strsql
>
>
> Exit_Command35_Click:
>    Exit Sub
>
> Err_Command35_Click:
>    MsgBox Err.Description
>    Resume Exit_Command35_Click
>
> End Sub
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
> 


0
Jeff
12/23/2009 5:45:12 PM
Hi Jeff I'm not sure how to test that as the SQl statement refres to controls
on a form and I'm not sure how to interpret the SQL in the VBA to SQL for a
query. The SQL does work for one of the forms and tables but not the other.
All I can say is that if I open the form from the list of objects by doublw
clicking on it to View then it shows the records OK.
Does that help?


Jeff Boyce wrote:
>Tony
>
>What happens if you use the SQL statement that's supposed to fill the second 
>form in a query instead?  Does it return any records?
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
>
>>I have some code that opens a form. If the code is based on one table and
>> form it works fine. If I try to run the same code against another table 
>[quoted text clipped - 66 lines]
>>
>> End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
TonyWilliams
12/23/2009 6:17:22 PM
Tony

If you copy the SQL statement, open a new query in design view, and paste 
the SQL statement into the SQL view window, you should be able to run it as 
a query.

If the SQL refers to controls on forms, you can either 'hard-code' values 
into the query/SQL statement, for testing purposes, or you could make sure 
the form is open and contains the needed values before attempting to run the 
query.

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"TonyWilliams via AccessMonster.com" <u56994@uwe> wrote in message 
news:a10765f17ac80@uwe...
> Hi Jeff I'm not sure how to test that as the SQl statement refres to 
> controls
> on a form and I'm not sure how to interpret the SQL in the VBA to SQL for 
> a
> query. The SQL does work for one of the forms and tables but not the 
> other.
> All I can say is that if I open the form from the list of objects by 
> doublw
> clicking on it to View then it shows the records OK.
> Does that help?
>
>
> Jeff Boyce wrote:
>>Tony
>>
>>What happens if you use the SQL statement that's supposed to fill the 
>>second
>>form in a query instead?  Does it return any records?
>>
>>Regards
>>
>>Jeff Boyce
>>Microsoft Access MVP
>>
>>>I have some code that opens a form. If the code is based on one table and
>>> form it works fine. If I try to run the same code against another table
>>[quoted text clipped - 66 lines]
>>>
>>> End Sub
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
> 


0
Jeff
12/23/2009 9:18:02 PM
Hi Tony,

It may not help and I may be wrong but here my guess.

TonyWilliams wrote:
>Forms!frmMain!SubForm1.SourceObject = "subformIrish"

You have a Main Form and a SubForm1 and another subform call "subformIrish"
in Subform1, is this correct?

>Forms!frmMain!SubForm1.Form.RecordSource = strsql

Are you filtering "SubForm1" recordsource or are you filtering "subformIrish"
recordsource?

-- 
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

0
AccessVandal
12/24/2009 3:05:27 AM
Thanks Jeff I'll have a go at that.
Have a great Christmas and New Year!
Tony

Jeff Boyce wrote:
>Tony
>
>If you copy the SQL statement, open a new query in design view, and paste 
>the SQL statement into the SQL view window, you should be able to run it as 
>a query.
>
>If the SQL refers to controls on forms, you can either 'hard-code' values 
>into the query/SQL statement, for testing purposes, or you could make sure 
>the form is open and contains the needed values before attempting to run the 
>query.
>
>Regards
>
>Jeff Boyce
>Microsoft Access MVP
>
>> Hi Jeff I'm not sure how to test that as the SQl statement refres to 
>> controls
>[quoted text clipped - 23 lines]
>>>>
>>>> End Sub

-- 
Message posted via http://www.accessmonster.com

0
TonyWilliams
12/24/2009 11:19:53 AM
Sorry guys the second set of code DOES work not the first! 
Sorry to confuse.
Tony

TonyWilliams wrote:
>I have some code that opens a form. If the code is based on one table and
>form it works fine. If I try to run the same code against another table with
>only the table and destination form names changed the form opens but doesn't
>show any records. Could someone help me where to start looking please. Below
>(for those who want to read it!) is the two sets of code:
>
>Thanks for anyone who can help, I was hoping to get this to bed befiore
>Christmas :-(
>Tony
>
>This DOESN'T work:
>
>Private Sub Command35_Click()
>On Error GoTo Err_Command35_Click
>    Dim strsql As String
>    Dim strtxtcompany As String
>    Dim dattxtdate As Date
>    
>    dattxtdate = Me.txtmontha
>    strtxtcompany = Me.cmbselectcompany
>    strsql = "SELECT * FROM [tblmainIrish] " & _
>    "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>    "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>    "AND [txtcompany] = '" & strtxtcompany & "'"
>    Forms!frmMain!SubForm1.SourceObject = "subformIrish"
>
>Forms!frmMain!SubForm1.Form.RecordSource = strsql
>
>Debug.Print strsql
>
>Exit_Command35_Click:
>    Exit Sub
>
>Err_Command35_Click:
>    MsgBox Err.Description
>    Resume Exit_Command35_Click
>    
>End Sub
>
>This DOESN'T work:
>
>Private Sub Command35_Click()
>On Error GoTo Err_Command35_Click
>    Dim strsql As String
>    Dim strtxtcompany As String
>    Dim dattxtdate As Date
>    
>    dattxtdate = Me.txtmontha
>    strtxtcompany = Me.cmbselectcompany
>    strsql = "SELECT * FROM [tblmaintabs] " & _
>    "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>    "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>    "AND [txtcompany] = '" & strtxtcompany & "'"
>    Forms!frmMain!SubForm1.SourceObject = "subformFDA"
>
>Forms!frmMain!SubForm1.Form.RecordSource = strsql
>
>Debug.Print strsql
>
>Exit_Command35_Click:
>    Exit Sub
>
>Err_Command35_Click:
>    MsgBox Err.Description
>    Resume Exit_Command35_Click
>    
>End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
TonyWilliams
12/24/2009 11:29:44 AM
Hi. Because of the user interface I'm using Subform1 is in fact sibformIrish
and I have to define which frm the interface uses as the subform1 as it can
change depending on which subform the user sees. Sorry if that's not too
clear but I'm using a proprietory intrerface (caleed UI Builder from Opengate)
and I don't really understand how that works albeit the code DOES work on the
other subform that is mentioned in the second lot of code in my original post.
Soory it does say that the second code DOESN'T work when I should have said
it DOES.
So in answer to your question I want to filter the records from tblmainIrish
to populate subformIrish which the interface will recognise as Subform1.

The problem with being self taught (particularly at 65) is I don't hacve the
skill to explain many things in terms that you guys can follow, BUT I try!
Thanks for your interest and have a great festive season!
Tony

AccessVandal wrote:
>Hi Tony,
>
>It may not help and I may be wrong but here my guess.
>
>>Forms!frmMain!SubForm1.SourceObject = "subformIrish"
>
>You have a Main Form and a SubForm1 and another subform call "subformIrish"
>in Subform1, is this correct?
>
>>Forms!frmMain!SubForm1.Form.RecordSource = strsql
>
>Are you filtering "SubForm1" recordsource or are you filtering "subformIrish"
>recordsource?
>

-- 
Message posted via http://www.accessmonster.com

0
TonyWilliams
12/24/2009 11:35:04 AM
Ah, my guess was wrong. Ok, got your point.

I notice that the SQL string, there are no spaces before the "AND" but you
said it works for the second code. (sorry for not posting it earlier)

"WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
   "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
   "AND [txtcompany] = '" & strtxtcompany & "'"
I hope it's not a typo here. If it's not, you need to put a space before the
"AND" string to something like....

   "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
   " AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
   " AND [txtcompany] = '" & strtxtcompany & "'"
    ^ the space here

Since you use the "debug.print" to view the SQL string, why not copy and
paste the output string from the immediate window and post it here. There
might be a possible syntax error.

TonyWilliams wrote:
>Hi. Because of the user interface I'm using Subform1 is in fact sibformIrish
>and I have to define which frm the interface uses as the subform1 as it can
>change depending on which subform the user sees. Sorry if that's not too
>clear but I'm using a proprietory intrerface (caleed UI Builder from Opengate)
>and I don't really understand how that works albeit the code DOES work on the
>other subform that is mentioned in the second lot of code in my original post.
>Soory it does say that the second code DOESN'T work when I should have said
>it DOES.
>So in answer to your question I want to filter the records from tblmainIrish
>to populate subformIrish which the interface will recognise as Subform1.
>
>The problem with being self taught (particularly at 65) is I don't hacve the
>skill to explain many things in terms that you guys can follow, BUT I try!
>Thanks for your interest and have a great festive season!
>Tony
>
>>Hi Tony,
>>
>[quoted text clipped - 9 lines]
>>Are you filtering "SubForm1" recordsource or are you filtering "subformIrish"
>>recordsource?

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
AccessVandal
12/24/2009 1:00:27 PM
Hi thanks for noticing the space, Put it in and got the same thing. When I
have run the code and opened the immediate window this is all there is:
24/12/2009 13:16:38 Loading Submenu from fLoadSubMenu() (debug logging turned
off)
24/12/2009 13:16:38 Loading Submenu-Complete from fLoadSubMenu() (debug
logging turned off)
It doesn't seem to be finding the string 
HOWEVER when I run the code that works this is what I get:
24/12/2009 13:16:38 Loading Submenu from fLoadSubMenu() (debug logging turned
off)
24/12/2009 13:16:38 Loading Submenu-Complete from fLoadSubMenu() (debug
logging turned off)SELECT * FROM [tblmaintabs] Where [txtmonthlabel] =
#September/2009#AND [txtcompany] = 'Aston Rothbury Factors Ltd'

Is that any help?
Thanks for looking at this on Christmas Eve!
Tony

AccessVandal wrote:
>Ah, my guess was wrong. Ok, got your point.
>
>I notice that the SQL string, there are no spaces before the "AND" but you
>said it works for the second code. (sorry for not posting it earlier)
>
>"WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>   "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>   "AND [txtcompany] = '" & strtxtcompany & "'"
>I hope it's not a typo here. If it's not, you need to put a space before the
>"AND" string to something like....
>
>   "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>   " AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>   " AND [txtcompany] = '" & strtxtcompany & "'"
>    ^ the space here
>
>Since you use the "debug.print" to view the SQL string, why not copy and
>paste the output string from the immediate window and post it here. There
>might be a possible syntax error.
>
>>Hi. Because of the user interface I'm using Subform1 is in fact sibformIrish
>>and I have to define which frm the interface uses as the subform1 as it can
>[quoted text clipped - 17 lines]
>>>Are you filtering "SubForm1" recordsource or are you filtering "subformIrish"
>>>recordsource?
>

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
TonyWilliams
12/24/2009 1:19:57 PM
The differences in the processes are the source tables and forms are
different in the sql statments but in reality they both have the same field
names and control names as each other it's just that one table contains data
in Euros for Irish companies and the other table contains data in Sterling.
Really mystifing!

AccessVandal wrote:
>Ah, my guess was wrong. Ok, got your point.
>
>I notice that the SQL string, there are no spaces before the "AND" but you
>said it works for the second code. (sorry for not posting it earlier)
>
>"WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>   "AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>   "AND [txtcompany] = '" & strtxtcompany & "'"
>I hope it's not a typo here. If it's not, you need to put a space before the
>"AND" string to something like....
>
>   "WHERE [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>   " AND [txtmonthlabel] = #" & Format(dattxtdate, "mmmm/yyyy") & "#" & _
>   " AND [txtcompany] = '" & strtxtcompany & "'"
>    ^ the space here
>
>Since you use the "debug.print" to view the SQL string, why not copy and
>paste the output string from the immediate window and post it here. There
>might be a possible syntax error.
>
>>Hi. Because of the user interface I'm using Subform1 is in fact sibformIrish
>>and I have to define which frm the interface uses as the subform1 as it can
>[quoted text clipped - 17 lines]
>>>Are you filtering "SubForm1" recordsource or are you filtering "subformIrish"
>>>recordsource?
>

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
TonyWilliams
12/24/2009 1:24:48 PM
That's a wierd output. I would not be able to help with the "UI Builder from
Opengate" and I don't know much about this addin product. It should be
displaying the SQL string.

However, the last output syntax is missing a space "#September/2009#AND
[txtcompany]" between the "#" and "AND", surprisingly it works in your case.
You might want to check the SQl string in the code or try it out with a space.


I'm not certain it the date issue, but you might want to use a different
format instead. Like

#09/2009# Format(dattxtdate, "mm/yyyy") or 
#Sep/2009# Format(dattxtdate, "mmm/yyyy") or 
#Sep/09# Format(dattxtdate, "mmm/yy")  or even
#2009/Sep# Format(dattxtdate, "yyyy/mmm") 

TonyWilliams wrote:
>Hi thanks for noticing the space, Put it in and got the same thing. When I
>have run the code and opened the immediate window this is all there is:
>24/12/2009 13:16:38 Loading Submenu from fLoadSubMenu() (debug logging turned
>off)
>24/12/2009 13:16:38 Loading Submenu-Complete from fLoadSubMenu() (debug
>logging turned off)SELECT * FROM [tblmaintabs] Where [txtmonthlabel] =
>#September/2009#AND [txtcompany] = 'Aston Rothbury Factors Ltd'

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
AccessVandal
12/24/2009 1:42:09 PM
Thanks for the quick response. I have tried  all the different date formats
and it doesn't make any difference. What I cannot understand is that the code
works against one table and form and doesn't when I change the table and form
names. As I said the tables and forms are exactly the same( the Irish one has
a few extra fields) so theoretically what should work on one should work for
the other. I think it's "needle in a haystack" time? :-(
Thanks
Tony
AccessVandal wrote:
>That's a wierd output. I would not be able to help with the "UI Builder from
>Opengate" and I don't know much about this addin product. It should be
>displaying the SQL string.
>
>However, the last output syntax is missing a space "#September/2009#AND
>[txtcompany]" between the "#" and "AND", surprisingly it works in your case.
>You might want to check the SQl string in the code or try it out with a space.
>
>I'm not certain it the date issue, but you might want to use a different
>format instead. Like
>
>#09/2009# Format(dattxtdate, "mm/yyyy") or 
>#Sep/2009# Format(dattxtdate, "mmm/yyyy") or 
>#Sep/09# Format(dattxtdate, "mmm/yy")  or even
>#2009/Sep# Format(dattxtdate, "yyyy/mmm") 
>
>>Hi thanks for noticing the space, Put it in and got the same thing. When I
>>have run the code and opened the immediate window this is all there is:
>[quoted text clipped - 3 lines]
>>logging turned off)SELECT * FROM [tblmaintabs] Where [txtmonthlabel] =
>>#September/2009#AND [txtcompany] = 'Aston Rothbury Factors Ltd'
>

-- 
Message posted via http://www.accessmonster.com

0
TonyWilliams
12/24/2009 2:10:09 PM
Is there anything I copuld print off to compare and possibly send you to have
a look at for me? I know that's pushing the bounds a little this this is
soooo frustrating. If I was 40 years younger I'd take it in my stride, go out
and have a beer and start again!
Thanks again
Tony 

AccessVandal wrote:
>That's a wierd output. I would not be able to help with the "UI Builder from
>Opengate" and I don't know much about this addin product. It should be
>displaying the SQL string.
>
>However, the last output syntax is missing a space "#September/2009#AND
>[txtcompany]" between the "#" and "AND", surprisingly it works in your case.
>You might want to check the SQl string in the code or try it out with a space.
>
>I'm not certain it the date issue, but you might want to use a different
>format instead. Like
>
>#09/2009# Format(dattxtdate, "mm/yyyy") or 
>#Sep/2009# Format(dattxtdate, "mmm/yyyy") or 
>#Sep/09# Format(dattxtdate, "mmm/yy")  or even
>#2009/Sep# Format(dattxtdate, "yyyy/mmm") 
>
>>Hi thanks for noticing the space, Put it in and got the same thing. When I
>>have run the code and opened the immediate window this is all there is:
>[quoted text clipped - 3 lines]
>>logging turned off)SELECT * FROM [tblmaintabs] Where [txtmonthlabel] =
>>#September/2009#AND [txtcompany] = 'Aston Rothbury Factors Ltd'
>

-- 
Message posted via http://www.accessmonster.com

0
TonyWilliams
12/24/2009 2:13:10 PM
Did Jeff suggestion to use it in the query editor work?

Right now I don't have MS Access to even view your db. But is it possible
that you don't use the UI Builder? The debug.print sql should display the sql
string.

I don't use my personal e-mail for Access help. But i might be able to use
another e-mail but only after christmas. I'll be out of the forum after
christmas, so see ya! and a merry christmas to you.

TonyWilliams wrote:
>Is there anything I copuld print off to compare and possibly send you to have
>a look at for me? I know that's pushing the bounds a little this this is
>soooo frustrating. If I was 40 years younger I'd take it in my stride, go out
>and have a beer and start again!
>Thanks again
>Tony

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
AccessVandal
12/24/2009 2:29:51 PM
Hi yes the sql worked in the query editor. I could ditch UI Builder but it
seems like a big step just because one query won't work as I would have to
recode all the queries, forms and reports to reflect the forms standing on
their own and not within UI Builder.
Have a great one!
Tony

AccessVandal wrote:
>Did Jeff suggestion to use it in the query editor work?
>
>Right now I don't have MS Access to even view your db. But is it possible
>that you don't use the UI Builder? The debug.print sql should display the sql
>string.
>
>I don't use my personal e-mail for Access help. But i might be able to use
>another e-mail but only after christmas. I'll be out of the forum after
>christmas, so see ya! and a merry christmas to you.
>
>>Is there anything I copuld print off to compare and possibly send you to have
>>a look at for me? I know that's pushing the bounds a little this this is
>>soooo frustrating. If I was 40 years younger I'd take it in my stride, go out
>>and have a beer and start again!
>>Thanks again
>>Tony
>

-- 
Message posted via http://www.accessmonster.com

0
TonyWilliams
12/24/2009 2:39:22 PM
Hi Tony,

If it works in the query editor, the sql syntax should work in the code. I'm
not sure why it wouldn't in your UI Builder.

Even you you gave me the db sample, I believe your db sample will work in my
computer.

So far, have you tried it out without the UI Builder? 

TonyWilliams wrote:
>Hi yes the sql worked in the query editor. I could ditch UI Builder but it
>seems like a big step just because one query won't work as I would have to
>recode all the queries, forms and reports to reflect the forms standing on
>their own and not within UI Builder.
>Have a great one!
>Tony

-- 
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

0
AccessVandal
12/27/2009 2:45:51 AM
Tony,

I forgot to ask about the Form's Master and Child Link. Your sql string may
be working correctly and it works in the Query Editor. What about the Main
Form and Subform Master/Child Link? Or the Relationship between the two
tables, I guess the data being filtered by the Subform did not match the data
with the Main Form. 

You can try to use the Query Editor with the main Table and the Child Table
instead of just the Child Table alone. Go to the QE to include the Main Table
and Child Table and create the criteria you had posted. See if the data match.


TonyWilliams wrote:
>Hi yes the sql worked in the query editor. I could ditch UI Builder but it
snip..

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
AccessVandal
12/28/2009 12:56:59 AM
Hi hope you had a great festive season!

The form and subform link are all part of the UI Builder interface. So the
main Table and Child table are part of the interface and not my data tables.
The interface picks the content of the subform from this line 
Forms!frmMain!SubForm1.SourceObject = "subformIrish"

and gets its records from the sql. I'm only filtering on one table on which
my form form (subformIrish) is based.

What really gets me is that when I use the code on the English table and
subform it works fine. But when I use the Irish table, which is a copy of the
English table but with values in Euros, and the Irish subform, which is a
copy of the English form but gets its data from the Irish table, it doesn't.

It may well ahve something to do with the UI Builder interface but I'm not
sure that it is as if it was I would have thought it wouldn't have worked at
all and all the other forms I use within the interface work just fine.

I'm begiining to think I may have to revisit the process! 

Regards and Happy New Year!
Tony
AccessVandal wrote:
>Tony,
>
>I forgot to ask about the Form's Master and Child Link. Your sql string may
>be working correctly and it works in the Query Editor. What about the Main
>Form and Subform Master/Child Link? Or the Relationship between the two
>tables, I guess the data being filtered by the Subform did not match the data
>with the Main Form. 
>
>You can try to use the Query Editor with the main Table and the Child Table
>instead of just the Child Table alone. Go to the QE to include the Main Table
>and Child Table and create the criteria you had posted. See if the data match.
>
>>Hi yes the sql worked in the query editor. I could ditch UI Builder but it
>snip..
>

-- 
Why don't my grey cells communicate with each as fast as they used to? I hate
getting old!

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
TonyWilliams
12/28/2009 10:22:49 AM
Thanks Tony, you too.

I don't know why it didn't work (with the UI Builder?). Have you look into
the English and Irish Tables data and how are they related to the Main Table.

So far, did it work on the query I had suggested? (Main Table and Irish Table)


Maybe you need to include additional code for the LinkChild/Master Fields.

Forms!frmMain!SubForm1.Form.LinkChildFields = "SomeID Field on subform"

Forms!frmMain!SubForm1.Form.LinkMasterFields = "SomeID Field on Mainform"

If you're still having problems, let me know and put a email to to you to
look at it.

TonyWilliams wrote:
>Hi hope you had a great festive season!
>
>The form and subform link are all part of the UI Builder interface. So the
>main Table and Child table are part of the interface and not my data tables.
>The interface picks the content of the subform from this line 
>Forms!frmMain!SubForm1.SourceObject = "subformIrish"
>
>and gets its records from the sql. I'm only filtering on one table on which
>my form form (subformIrish) is based.
>
>What really gets me is that when I use the code on the English table and
>subform it works fine. But when I use the Irish table, which is a copy of the
>English table but with values in Euros, and the Irish subform, which is a
>copy of the English form but gets its data from the Irish table, it doesn't.
>
>It may well ahve something to do with the UI Builder interface but I'm not
>sure that it is as if it was I would have thought it wouldn't have worked at
>all and all the other forms I use within the interface work just fine.
>
>I'm begiining to think I may have to revisit the process! 
>
>Regards and Happy New Year!
>Tony

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

0
AccessVandal
12/29/2009 1:04:45 AM
Reply:

Similar Artilces:

how to move from our current exchange server to a new one???
We are moving from an exchange active passive cluster to a single exchange server (brand new compaq). My plan (in theory) is to disconnect the current exchange server, build the new exchange server with the same name, install exhange in disaster recovery mode, and then restore the databases from tape. Does this sound correct to you guys? Are there any documents that would apply to this situation? thanks, Matt Why keep the same name? Build your new server alongside the old and move the mailboxes across. If you're worried about the new server name meaning that you'll need to v...

Dynamic Sql not working when variable not passed
I have a stored procedure that worked fine until I added variable to hold some values to put in my new "IN" clause. My SP *************************************************** CREATE PROCEDURE [dbo].testSP @Jobs varchar(300) = null AS DECLARE @Sql nvarchar(4000) SET @Sql = 'SELECT * FROM Temp WHERE (@Jobs is NULL OR JOB_ID in (' + @Jobs + '))' print @Sql ************************************************ If I run this with: testSP @Jobs = '210,220' I get: SELECT * FROM Temp WHERE (@Jobs is NULL OR JOB_ID in (210,220)) W...

using Interoperation to call Managed code from COM
Hi! This question is just for understanding. I have found this example in Code project. I have a COM component and a .NET class library. The COM will call the .NET type library that is sitting in between the COM and the .NET library and the type library will call the .NET library. This type library is sort of encapulating the .NET library The .NET library has as the extension as all know the famous DLL and the type library has extension TLB. I have noticed that the .NET library must be registered in the registry. But what about the type library that is sort of a COM thingy does t...

Help! I'm very new at this, working with Access 2000
I am creating a database for complaints. I have a main table for Complaints, and a joined table for Illness Details. Part of Illness details are symptoms, and a person can have many symptoms. I created a separate table for Symptoms, linking it with the Illness Detail ID. When I try to print a report with onesection for symptoms,(all symptoms in same box) I can not get the multiple symptoms to print in that box. If I have more than one symptom, my report will only print one symptom for each line, repeating all the illness detail information for each complaint . -- Nancy B. Please...

multiple correlations with one click?
I have a numerical data set with 11 columns and 1809 rows. I would like to simply obtain a table of correlations involving Col 1 with each remaining Col (Col 2 thru Col 11), then Col 2 with each remaining Col (3 thru 11), then Col 4 with each remaining Col (5 thru 11), and so on until the last correlation, i.e., Col 10 with Col 11. I know what a correlation is but I have no idea what you want. Define "correlation" in your context. IOW, what do you want to see as the result? HTH Otto "bondjel" <bondjel@discussions.microsoft.com> wrote in message news:...

how to copy GAL from one exchange organization to another
Hello, How do i copy the GAL from one exchange server 2003 in one forest to another exchange server 2003 in another forest urgent pls Are you talking about syncing them on a regular basis? Look into the Identity Integration Feature Pack for reocurring are the following for a one time dump: http://www.imibo.com/imidev/Exchange/imige.htm Nue "tayoajax" <tayoajax@discussions.microsoft.com> wrote in message news:41413990-5955-4FE5-80AC-AC881064450F@microsoft.com... > Hello, > > How do i copy the GAL from one exchange server 2003 in one forest to > another >...

Copying cells working only sporadically
I am having a strange phenomenon since I installed MS Office to an older Windows XP system I have. Copying cells in Excel has become ahit or miss proposition. I finally figured out that when I attempt to copy cells, for an unknown reason, a large majority of the time it now copies the cells as embedded objects. This happens for long periods of time making Exel worthless and then mysteriously it goes away. I think this same situation is affecting other programs. My theory is the MS Office install has somehow damaged normal Windows XP processing of clipboard copies. 70% of th...

Table Place settings design
We are having a company dinner party to say goodbye to the oldest member of company. 70 yrs old We want to print a place card with photograph and some words on it for 50 employees. Sounds like a Tent Card would meet the need. If you were using Publisher 2000 (your version may be similar) it would be, File, Page Setup Special Fold Tent Card (From the Choose a Special Fold menu) Choose an Orientation Portrait or Landscape, your preference OK If you plan to use other than 8�"x11" stock, do first, select File Print Setup Provide the stock size as you follow the Setup steps. --...

Find Modified Date of a Table
How can I find a tables modified date. I am using filedatetime for files. How do you use it for tables? On Fri, 02 Nov 2007 21:11:22 -0500, Lillian <libertysmith10@yahoo.com> wrote: Assuming your tablename is Employees, you could write: Debug.Print DBEngine(0)(0).TableDefs("Employees").Properties("LastUpdated") -Tom. >How can I find a tables modified date. >I am using filedatetime for files. >How do you use it for tables? Unfortunately, I don't believe that works in versions of Access since Access 97. -- Doug Steele, Microsoft Access MVP http://...

Anyone know when Oulook 2003 Contact Form Designer will have an update ? Or a workaround?
Anyone know when Oulook 2003 Contact Form Designer will have an update ? Or a workaround? I am trying to create a custom contact form in Outlook 2003 and want to base it off of the default contact form in Outlook 2003 and just get the old XP form. I went to knowledge base and they say that it is a known problem that the design form for Outlook contacts 2003 (only the contact form apparently) was not updated from 2000 or XP. Does anyone know when that will be fixed or have another workaround? I would like the new form to have the picture option that the default Outlook 2003 contact form has. ...

Printing with more than one column per page
I'm using the term, "column" incorrectly here. I want to print a list of records, but I want to have 2 or three "columns" of records per page. Word can do this using the "columns" control, but I can't figure out how to get Excel to do this, or how to get Word to import an Excel file such that I can do this. The item is an electronic price list, and it's going to be over 100 pages if each line is one record, and it's mostly blank space. Thanks for any suggestions. I'm using Excel and Word '97. Regards, Robin Robin, My Excel add-in &quo...

Excel 2002 Filters stop working
I'm using the filter function in Excel but the drop downs stop working. Show all doesn't bring back the functionality the only way to fix this seems to be turning off all filters and setting up the autofilter again. Any ideas what could be causing this and if there is a fix? #1. Is your worksheet protected? If it is protected, maybe you didn't set the option to enable autofilter? #2. Have you been playing around with range names--deleting them???? Maybe you deleted one that excel likes???? kwarner@cisco.com wrote: > > I'm using the filter function in Excel b...

Calendar control on protected form with multiple date fields
How do a reference the formfield on a protected form to insert a date from a datepicker? I have 3 date fields, it seems I can insert a date based on the field selected i.e. selection.formfields("x")=calendar1.value Any help is appreciated. thanks -- See http://www.gmayor.com/popup_calendar.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

Passing a value from subform or main form to subform query
I am using MS ACCESS 2003 I have a main form and a subform that pulls up an existing case for the user to update the information already entered. The subform is designed and opens up in form view not in datasheet view. The subform knows which case to pull in based on the case number on the main form The textboxes on the main form are bound fields from a query. The user enters an ID which is how the main form query knows which record to pull and it works. The textboxes on the subform are bound with data from a different query (so I have a query for each form) The que...

Query from two tables duplicating data
I have created a query in Access 2002 using fields from two tables. The query is duplicating data so that each line item is listed multiple times. I can specify with more details if someone thinks they can help. Can you? Post the SQL of the query. If you are not familiar with how to do that, open your query in desingn mode, switch to SQL view, and copy/paste the code into your post. -- Dave Hargis, Microsoft Access MVP "Rachael" wrote: > I have created a query in Access 2002 using fields from two tables. The query > is duplicating data so that each line item is liste...

customizing chart column color formats in code
I have some code that examines a set of data and generates a x1columncluster chart. Depending on the set of data, I may have any number of column clusters as a result. However, I currently format the columns manually to make certain groupings a given color and another group of columns a different color and so forth. I would like to automate this. Is there a way to go through each column like for x = 1 to numberOfColumns check group If group = 1 set color accordingly else set color differently end if Did that make sense? TIA Perhaps you could use Jon Peltier's technique for conditio...

Excel File will not open if Excel is no open first
Hi, I am having problems where double clicking an Excel file will launch Excel, but it will not open the file itself. This goes for all my Excel files. Once Excel is open I can use File->Open to open the file, but I do not know why it won't open the file in the first place. Obviously there is nothing wrong with my file associations since it still launches Excel when I double click the file icons. I am not running any macros and also do not have any add- ins installed. Any advice on how to fix the problem will be greatly appreciated. Thanks Vyyk Hi Vyyk, > I am having ...

Is WideCharToMultiByte(...) works fine If unicode char is more than 2 byte???
Hi All, I have one confusion whether WideCharToMultiByte(....) function works properly if the uni char is of more than 2 bytes? .....Thanks & Regards, Sajal Sajal wrote: > Hi All, > I have one confusion whether WideCharToMultiByte(....) function works > properly if the uni char is of more than 2 bytes? > > ....Thanks & Regards, > Sajal > Sajal: I am confused about this also. Looking at the docs in VS2003 I do not see anything that addresses this question. David Wilkinson You mean does it work for Unicode surrogates? I saw something about Unicode 4.1 and...

xslt not working when xml has xmlns="urn" in root element.
Hello guys, I am new to XML and working on a XSLT to transforn yahoo shopping search result to html. my problem is the return XML contain xmlns in root element, here is the sample xml: <?xml version="1.0" encoding="UTF-8"?> <?xml-stylesheet type="text/xsl" href="aproductsearch.xslt"?> <ProductSearch xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:yahoo:aprods" xsi:schemaLocation="urn:yahoo:aprods http://api.shopping.yahoo.com/shoppingservice/v2/aproductsearch.xsd"> ................... <...

Table Storing Default Site ID?
I'm trying to find the table that stores the Default Site ID in the Item Quantities Maintenance window. I thought it would be in IV00102, but it doesn't seem to be. Any help would be greatly appreciated! Thanks, Craig It's in the Item Master table (IV00101), in the LOCNCODE field. -- GP 9.0, SP2 "Craig" wrote: > I'm trying to find the table that stores the Default Site ID in the Item > Quantities Maintenance window. I thought it would be in IV00102, but it > doesn't seem to be. > > Any help would be greatly appreciated! > > Than...

format in MS Access chart data table
iI created a chart from access table. The format of data is stored in percentage with 2 dec. format. When I creat the chart with data table displayed, the 9.8% diplay as .098 in the data table. Even I changed the number in data sheet to %, it appears as 9.8% in design view, but when I switch to the data view, ir changed back to .0980. I managed to change the axis and data label to % format, but it will not work with the data table. I have another chart with currency in 6 dec. format, the data in the data table shows in scientific format. Is anyone willing to help me to fix this ...

how can I post to GL from custom form ?
Hello there is there any guide, or example of a posting from a custom form ? I needs ot have a cusotme form to take some data from another DB or customer tables and then save it in some journal then post it .... ? anybody done that before / or is it a plain crazy idea ? You may want to look at eOne Solutions' version of Extender (Enterprise Edition). It allows one to create forms easily and tie them in to various transactions in Great Plains. They provide a number of sample application that will give you an idea of the functionality and how to build it. http://www.eonesoluti...

OLE Objects display mode in form
I have a form in which the OLE Objects (saved emails) display as only "package". I have the field settings as size mode: clip and Display Type:Icon. Why won't the objects display in the Icon format wth the email file name displayed? ...

View HTLM function not working
When I try to view the HTML source code in an HTML email I wrote, right click the text, select "view source" but nothing happpens. An suggestions out there?? Thanks, Ji ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

Pivot Table Enhancements/Fixes in Excel 2003
We are debating migrating from Excel 2002 to 2003 to address some issues we have encountered using pivot tables running against Analysis Services cubes using pivots in Microsoft Excel 2002. I'm searching around for a comprehensive list of enhancements and fixes for Excel 2003 specifically related to the pivot table functionality and I'm having some trouble finding anything. Can you send me a link to such a list (or just the list itself) - we are specifically interested in know about any actual fixes that have gone into this version. Thanks I don't recall if there are any...