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

[TonyWilliams via AccessMonster.com]

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

 

[Jeff Boyce]

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]

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?

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

 

[Jeff Boyce]

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.

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?

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

 

[AccessVandal via AccessMonster.com]

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?

 

[TonyWilliams via AccessMonster.com]

Thanks Jeff I'll have a go at that.
Have a great Christmas and New Year!
Tony

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]

 

[TonyWilliams via AccessMonster.com]

Sorry guys the second set of code DOES work not the first!
Sorry to confuse.
Tony

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

 

[TonyWilliams via AccessMonster.com]

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 via AccessMonster.com]

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
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?

 

[TonyWilliams via AccessMonster.com]

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

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]

 

[TonyWilliams via AccessMonster.com]

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!

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]

 

[AccessVandal via AccessMonster.com]

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:
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'

 

[TonyWilliams via AccessMonster.com]

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

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'

 

[TonyWilliams via AccessMonster.com]

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

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'

 

[AccessVandal via AccessMonster.com]

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 via AccessMonster.com]

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 via AccessMonster.com]

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?

 

[AccessVandal via AccessMonster.com]

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..

 

[TonyWilliams via AccessMonster.com]

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!

 

[AccessVandal via AccessMonster.com]

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.