please help

  • Thread starter Thread starter MsWatts
  • Start date Start date
M

MsWatts

I am having trouble with my form pulling info from my query. It will run fine
if I don't have the forms!formname!controlname there but once I add that it
won't pull at all. Does anybody have a clue why its not pulling?
 
I am having trouble with my form pulling info from my query.
Click to expand...

I think you mean "my query pulling info from my form."
It will run fine
if I don't have the forms!formname!controlname there but once I add that it
won't pull at all. Does anybody have a clue why its not pulling?
Click to expand...

You might post the SQL of your query so that we can take a look.

You might want to check the syntax and value of your Forms reference
by typing it into the immediate window (press Ctl-G). Begin with a
question mark, like this:
?Forms!Myformname!Mycontrolname

Is the form open? Does it return a value? Is the value what you
expected?

Then try replacing that reference with that hard-coded value just to
try it out in your query.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
The form has to be open and contain data in the text box when the query is
run. The query is run when you open the form. Chicken and egg!
Add action to requery after update of text box.
 
forms!formname!controlname must match the form and controlname exactly.
Also if there are any spaces or other special characters, you might need
square brackets.

forms![formname]![controlname]

The form must be open for it to work. It can be invisible, but needs to be
open.

The control must have data in it.

There can be problems with data types. For examlpe with a date field in the
query, you may need something like this to convert the text to a date:

"#" & forms![formname]![controlname] & "#"
 
Thanks for all the wonderful suggestions. I used the immediate window and I
was able to locate an error on one of my tables. I'm trying to fix that now.

I am unable to post my SQL because I am communicating via my Android phone
and I can't copy the text for some reason.

Also is there some type of code I can add to make the query ignore certain
fields when there is a null value?

Thanks,
Tammy Watts
 
Great point.

You'd think that I'd remember after living near Oxford for 4 years.

Cheers!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


KenSheridan via AccessMonster.com said:
Jerry:

May work for you Mercans, but not for us Yurpeans. If the value in the
control is in short date format it would change 4th July to 7th April! Best
way is to declare the parameter:

PARAMETERS
Forms![formname]![controlname] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate = Forms![formname]![controlname];

All the best for the holiday,

Ken Sheridan
Stafford, England

Jerry said:
forms!formname!controlname must match the form and controlname exactly.
Also if there are any spaces or other special characters, you might need
square brackets.

forms![formname]![controlname]

The form must be open for it to work. It can be invisible, but needs to be
open.

The control must have data in it.

There can be problems with data types. For examlpe with a date field in the
query, you may need something like this to convert the text to a date:

"#" & forms![formname]![controlname] & "#"
I am having trouble with my form pulling info from my query. It will run fine
if I don't have the forms!formname!controlname there but once I add that it
won't pull at all. Does anybody have a clue why its not pulling?
Click to expand...
Click to expand...
Click to expand...
 
Ken thanks for all your help I was finally able to get the query to pull from
my form. However it isn't returning all the information due to the Null
values. Please see my code below. I am really having a tough time figuring
out what to do since I am fairly new to Access.

Option Compare Database

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "Test 2", acViewNormal, acEdit
DoCmd.Close acForm, "Packaging Query Form"
End Sub


Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click


DoCmd.Close

Exit_Close_Form_Click:
Exit Sub

Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click

End Sub
Private Sub Run_Click()
On Error GoTo Err_Run_Click

Dim stDocName As String

stDocName = "Test 2"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Run_Click:
Exit Sub

Err_Run_Click:
MsgBox Err.Description
Resume Exit_Run_Click

End Sub

Thanks for all the help you have provided thus far,

Tammy Watts



KenSheridan via AccessMonster.com said:
Tammy:

Putting:

Is Not Null

in the criteria row of a column in design view will mean that that the query
doesn't return that row if the column in question is Null. If you want to do
this for multiple columns then it needs to be on each column. If you don't
want the row returned where any of these are Null it has to be an OR
operation, which means putting the Is Not Null on separate lines in design
view. This could be tricky when combining these criteria with your parameter
reference to the form's control. If you don't want the row returned only
where all of these are Null it has to be an AND operation, which means
putting the Is Not Null on the same line in design view. That's less tricky
when combined with the parameter, but on the whole its easier to express this
sort of logic by writing it in SQL view. So we'd really need to see the SQL
and have a clear explanation of how you want the Nulls handled to give you
detailed advice.

Ken Sheridan
Stafford, England
Thanks for all the wonderful suggestions. I used the immediate window and I
was able to locate an error on one of my tables. I'm trying to fix that now.

I am unable to post my SQL because I am communicating via my Android phone
and I can't copy the text for some reason.

Also is there some type of code I can add to make the query ignore certain
fields when there is a null value?

Thanks,
Tammy Watts
Also try declaring the parameter, particularly if it’s a date. A date
entered in the form in a format such as 12/22/2009 might be misinterpreted as
Click to expand...
[quoted text clipped - 17 lines]
if I don't have the forms!formname!controlname there but once I add that it
won't pull at all. Does anybody have a clue why its not pulling?
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
KARL DEWEY said:
The form has to be open and contain data in the text box when the query is
run. The query is run when you open the form. Chicken and egg!
Add action to requery after update of text box.
Click to expand...
 
Back
Top