Problems running a query from a text box / type in new info gives same answer

[Target P via AccessMonster.com]

Hi new to forum..looks great..also v new to Access and SQL etc...

I have created a form whereby you input your search criteria into a text box ,
click on a command button and it submits the query and returns and answer in
the data sheet below.

If I type new criteria into the text box and click the command button again
it does not return any new data (I know it should). but either a blank space
or whats already there.

What do I currently do?

I have a list of postcodes in Essex each one has a result that states YES I
COVER THIS AREA or NO I DO NOT COVER THIS AREA.

I type into my text box the postcode e.g CM3 and the result will display
(currently as datasheet)

If I then retype in the text box a different postcode the same result comes
back when it should not.

What do I want to do?-

1) Put a command button on my form that will clear the text box and clear
the query thats already been run so its ready for me to input next data and
run the query again based on the new info in the text box (data is not saved
btw its reference only and I believe this is all unbound).

2) Instead of the results of the run query showing up as a data sheet how
would I force the results into a text box or area within a form?


thanks anyone who reads and resonds to this...

TP

 

[Gijs Beukenoot]

Target P via AccessMonster.com schreef op 31-8-2005 :

Hi new to forum..looks great..also v new to Access and SQL etc...

I have created a form whereby you input your search criteria into a text box
, click on a command button and it submits the query and returns and answer
in the data sheet below.

If I type new criteria into the text box and click the command button again
it does not return any new data (I know it should). but either a blank space
or whats already there.

What do I currently do?

I have a list of postcodes in Essex each one has a result that states YES I
COVER THIS AREA or NO I DO NOT COVER THIS AREA.

I type into my text box the postcode e.g CM3 and the result will display
(currently as datasheet)

If I then retype in the text box a different postcode the same result comes
back when it should not.

What do I want to do?-

1) Put a command button on my form that will clear the text box and clear
the query thats already been run so its ready for me to input next data and
run the query again based on the new info in the text box (data is not saved
btw its reference only and I believe this is all unbound).

2) Instead of the results of the run query showing up as a data sheet how
would I force the results into a text box or area within a form?


thanks anyone who reads and resonds to this...

TP

Put another textbox on the form, call it txtCoverage
Put a button on the form, cancel the wizard (if any) and go to the
properties to remove the picture (if any) and modify the caption to
"Check". Then modify the "default" property of the button so it is the
default button (if you press enter onm the form, this button is
pressed)
Then go to the click event. Click on the 3-dotted-button.
Choose code. The VBa window opens up with the Click event of your
button. Type (or copy) the following

me.txtCoverage = "Unknown entry"
if nz(Me.txtBoxWhereYouEnterTheCode,"") <> "" Then
me.txtCoverage = dlookup("Field1", "Table", "Field2='" &
Me.txtBoxWhereYouEnterTheCode & "'")
endif
me.txtBoxWhereYouEnterTheCode.setfocus
me.txtBoxWhereYouEnterTheCode = ""

Field1 is the field in the database that holds YES I COVER THIS AREA or
the NO I DO NOT COVER THIS AREA
Field2 is the name of the field holding the postalcode
The Me.txtCoverage is to make sure that when you enter an unknown
postalcode, the text on screen is different indicating no record has
been found.
Then it checks to see if any data is entered in
Me.txtBoxWhereYouEnterTheCode
If there is data, the dlookup searches the table "Table" (so you want
to rename that to the correct table) for a field called "Field2" that
is equal to the text of Me.txtBoxWhereYouEnterTheCode. It will return
the value from "Field1" so change that to the field holding the
coverage-thingie
Afterwards, it sets the focus back to the Me.txtBoxWhereYouEnterTheCode
and empties it for the next entry

 

[Target P via AccessMonster.com]

Thanks Gijs, for your reply I will certainly try out what you have written, I
appreciate you taking the time to write it out and answer.

Is VB much different from SQL? I am eventually going to link a lot of these
queries into a FP ASP webpage using access as the DB so I guess Ill need to
learn a little programming to get it working properly and link the buttons
correctly there...

tks vm

TP

Target P via AccessMonster.com schreef op 31-8-2005 :

Hi new to forum..looks great..also v new to Access and SQL etc...

[quoted text clipped - 30 lines]

TP

Put another textbox on the form, call it txtCoverage
Put a button on the form, cancel the wizard (if any) and go to the
properties to remove the picture (if any) and modify the caption to
"Check". Then modify the "default" property of the button so it is the
default button (if you press enter onm the form, this button is
pressed)
Then go to the click event. Click on the 3-dotted-button.
Choose code. The VBa window opens up with the Click event of your
button. Type (or copy) the following

me.txtCoverage = "Unknown entry"
if nz(Me.txtBoxWhereYouEnterTheCode,"") <> "" Then
me.txtCoverage = dlookup("Field1", "Table", "Field2='" &
Me.txtBoxWhereYouEnterTheCode & "'")
endif
me.txtBoxWhereYouEnterTheCode.setfocus
me.txtBoxWhereYouEnterTheCode = ""

Field1 is the field in the database that holds YES I COVER THIS AREA or
the NO I DO NOT COVER THIS AREA
Field2 is the name of the field holding the postalcode
The Me.txtCoverage is to make sure that when you enter an unknown
postalcode, the text on screen is different indicating no record has
been found.
Then it checks to see if any data is entered in
Me.txtBoxWhereYouEnterTheCode
If there is data, the dlookup searches the table "Table" (so you want
to rename that to the correct table) for a field called "Field2" that
is equal to the text of Me.txtBoxWhereYouEnterTheCode. It will return
the value from "Field1" so change that to the field holding the
coverage-thingie
Afterwards, it sets the focus back to the Me.txtBoxWhereYouEnterTheCode
and empties it for the next entry