An action when a SQL string doesn't match in a recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi

i have a SQl string in a code like the one belo

dim db as DAO.databas
dim rs as DAO.recordse
dim strSQl as strin

set db= currentD

strSQL = "Select * from Table1 where ((Table1.field1 < " & me.textbox1 & "));

set rs = db.openrecordset(strSQL
rs.movefirs

code = rs!p

rs.movelas

---------------------------------------------
it's a normal criteria comparing a field from the table with a field from my form.
the way the code is written, it will get the first occurence and will store in variable cod

this will go tiil the last record with a few more occurences.

however, when there is no occurences (the strSQL dont produce any result) i want tha
at least the last record will be displayed

And that's my problem. I don't know how can i handle and control the last record in a way
can get the values from my recordset and store them in my form.

maybe it's a simple issue and i am complicating it.

Can anybody helps me

Thank
Zeca Nogueira

However , when there
 
Comments in line
it's a normal criteria comparing a field from the table with a field from my form.
Click to expand...

Which limits the number of records returned (filters the recordset)
the way the code is written, it will get the first occurence and will store in variable cod
Click to expand...

IF there are any records that meet the criteria. If [field1] contained $$$ deposits in a bank account and you entered "Rover" in textbox1, then you should expect the recordset to contain zero records
this will go tiil the last record with a few more occurences.
Click to expand...
however, when there is no occurences (the strSQL dont produce any result) i want tha
at least the last record will be displayed
Click to expand...

What is the 'Last" (or the "First") record of zero records? "the strSQL dont produce any result" means there were no records that met the criteria
And that's my problem. I don't know how can i handle and control the last record in a way
can get the values from my recordset and store them in my form.
Click to expand...

In this snippet of code, it is hard to understand what you are trying to do. You do need to check to see if there are records in the recordset before you try to get data from a field. See the code below
maybe it's a simple issue and i am complicating it.
Click to expand...

You didn't give much to work with. It would help if you would post more of the code and tell what the problem is and what you want to happen


'------ modified -----
strSQL = "Select * from Table1 where ((Table1.field1 < " & me.textbox1 & "));

set rs = db.openrecordset(strSQL
If Not rs.BOF and Not rs.EOF Then '*****Adde
rs.movefirs
code = rs!p
rs.movelas
Else '****Adde
Msgbox "No records met the criteria!!" '****Adde
' or you could just use '****Adde
'Exit Sub '****Adde
End If '****Adde
---------------------------------------------

Sometimes, defining the problem is half of the solution........

HT

MCR
 
Thanks for your prompt and kindly reply.

The defintition of what i want is the following .

i want to get data from a table, based on a SQL string . If after read all the table, there is any record that match the criteria on the SQL string, i will go to the last record and get all the information from that record (the last)

Why the last record? Because it is what i want to happen

---------------------------------------------------------------

The code is the following

Dim db As DAO.Databas
Dim rst As DAO.Recordse
Dim varSQL As Strin

Set db = CurrentD

varSQL = "SELECT * FROM TLajes WHERE ((((((((TLajes.Vigota = " & Me.ALTVIGOTA & ")) AND (TLajes.Altura = " & Me.ALTPAVIM & ")) AND (TLajes.MSD <= TLajes.MRD)) AND (TLajes.VSD <= TLajes.VRD)) AND (TLajes.MSFCTK <= TLajes.MFCTK)) AND (TLajes.FSLEXA <= 1.5)) AND (TLajes.FSLEXA <= " & calvao & "));

Set rst = db.OpenRecordset(varSQL
rst.MoveFirs

' get the values from the fields in the tabl

codigo = rst![Tipopavimento
xpp = rst!p
xmrd1 = rst!MR
xvrd1 = rst!VR
xmfctk1 = rst!MFCT
xmsfctk = rst!msfct
xei = rst!E
xsd = rst!S
xlarg = rst!Largur
xvig = rst!Vigot
xalt = rst!Altur
xarm = rst!Ardis
xmsd = rst!MS
xvsd = rst!VS
xflexa = rst!fslex
xhc = rst!H

rst.MoveLas

' move the fields to the textboxs in the for

Me.TIPO = codig
Me.PPPAVIM = xp
Me.CARGASPERM = Me.PARDIVIS + Me.REVESTIM + Me.OUTROS + Me.PPPAVI
Me.MRD = xmrd
Me.VRD = xvrd
Me.MFCTK = xmfctk
Me.EI = xe
Me.ESFMSD = xms
Me.ESFMFCTK = xmsfct
Me.DEFLPRAZO = xflex
Me.ESFAPOIOSVSD = xvs
Me.RECOBRIMENTO = xh

----------------------------------------------------------------

As you can see and expect , this code returns me a lot of records from table TLajes. However, there may have conditions where there is any record on the table matching the criteria on the varSQL. On such case i want to obtain the last record as a result that is acceptable

Please accept my appologies for the bad definition of my problem and of the question that i poste

Once again, accept my best regards and compliments for your hel

Zeca
 
Hi, again. I understand a little better. I have a couple of more questions. Here goes...

1) Is the form a bound form
2) If the form is boune, what is the recordsource for the form
3) Are the textboxes (Me.TIPO, Me.PPPAVIM, etc) bound or unbound

4)If the recordset (rs) returns 20 records, do you want to step thru all 20 records and put the data into the text boxes
Which would be in new records

5) If the recordset returns 0 records, What do you want to happen? Put up a message box telling you there were no records, close the form, ....?? (not why <grin> .... why = because :)

You need to test for the number of records in rs after opening the recordset by using the RecordCount property or testing for BOF and EOF. If you try to read a field in the recordset when there are no records, you will get an error

We'll get it soon. Why??? Because I'm........

StillLearning ;)
 
H

Thanks for your kindly reply and here i go with my answers

1) yes. it's a bound form

2)TPavimento

3) all textboxes are bounde

4) when a recordset match the criteria on my SQL string it displays its context on my form. i have an extra Query with the rest of the records that match the criteria.( A logical view of the table

5) I want the program goes to last record of my table and allow me to get the context of this record and display him in the apropriated textboxes in my form, exactly as i am doing now when a record matches the criteria on my SQL strin

i hope you are right. in the reality i need to count them (324) and see if is .EOF
I think this is the solution

A new SQLstring
IF recordcount = 324 AND rs.EOF = true AND "none record matches" = true THE
GET the fields of the recordID = 32
ENDI

the only problem is that i really dont know how to do the last part of the SQL string...

But, thanks God i am with someone who is STILL LEARNING...and teaching me a LO

Hope to answer and clarify the problem for yo

Once again, thanks for your help and understanding and calm
for keeping STILL TEACHING the others.

Best Regard
Zec
 
OK
so now I know that you have a form with a record source of table 'TPavimentos
and the textboxes on the form are bound to fields in table 'TPavimentos'

You have some code behind the form that you run (maybe with a button, maybe the OnOpen event of the form) that opens a recordset based on table 'TLajes' . The table 'TLajes' is filtered by controls (textboxes) that are on the form (the Where part of the SQL string)

When you run the code, if there is a record in the recordset 'rst', you put the data into memory variables. Then you want to goto the 'Last' record in the table 'TPavimentos' and copy the data from the memory variables into textboxes on the form

Since the record source for the form is not sorted, there is no real 'Last Record'. Think of a bucket of numbered sticks. Just because one time you pull out a stick with the number 10 on it, doesn't mean that the next time you will also pull out the number 10 stick. You might pull the number 35 or 2 or 51

The same holds for the table 'TLajes'. Because the record set is not sorted, you might not get the same record the first time as the second time, if you run the code 3 time (providing that there is more that 1 record that meets the filter parameters)

It still sounds like you are getting data from table 'TLajes' and over-writing a record in table 'TPavimentos'.
Is this right??

With what I understand right now, the following code is the best I can show you

'--- snip --
Dim db As DAO.Databas
Dim rst As DAO.Recordse
Dim varSQL As Strin

Set db = CurrentD

varSQL = "SELECT * FROM TLajes WHERE ((((((((TLajes.Vigota = " & Me.ALTVIGOTA & ")) AND (TLajes.Altura = " & Me.ALTPAVIM & ")) AND (TLajes.MSD <= TLajes.MRD)) AND (TLajes.VSD <= TLajes.VRD)) AND (TLajes.MSFCTK <= TLajes.MFCTK)) AND (TLajes.FSLEXA <= 1.5)) AND (TLajes.FSLEXA <= " & calvao & "));

Set rst = db.OpenRecordset(varSQL
If Not rst.BOF And Not rst.EOF the
' record were found
rst.MoveFirs

' get the values from the fields in the tabl

codigo = rst![Tipopavimento
xpp = rst!p
xmrd1 = rst!MR
xvrd1 = rst!VR
xmfctk1 = rst!MFCT
xmsfctk = rst!msfct
xei = rst!E
xsd = rst!S
xlarg = rst!Largur
xvig = rst!Vigot
xalt = rst!Altur
xarm = rst!Ardis
xmsd = rst!MS
xvsd = rst!VS
xflexa = rst!fslex
xhc = rst!H

' move to the *Last* record of the record source for the for
Me.Recordset.MoveLas

' move the fields to the textboxs in the for
Me.TIPO = codig
Me.PPPAVIM = xp
Me.CARGASPERM = Me.PARDIVIS + Me.REVESTIM + Me.OUTROS + Me.PPPAVI
Me.MRD = xmrd
Me.VRD = xvrd
Me.MFCTK = xmfctk
Me.EI = xe
Me.ESFMSD = xms
Me.ESFMFCTK = xmsfct
Me.DEFLPRAZO = xflex
Me.ESFAPOIOSVSD = xvs
Me.RECOBRIMENTO = xh
Els
' no records returned for rst.
' move to the *Last* record of the record source for the for
Me.Recordset.MoveLas
End I

' Clean u
rst.Clos
Set rst = Nothin
Set db = Nothin

End Su
'---- snip ---

Sigh, more questions...

Can more than 1 record be returned from table 'TLajes'?
If more than 1 record, do you want to get the data for only the top record from the recordset 'rst', or loop thru all three and put the data into three records on the form?

Still Learnin
Still here :-D
 
Only a couple more questions and then I think it will work....

From a post by John W. Vinson[MVP] (and many other MVPs)

<snip>
A Table is an unordered "bag" of data; it HAS NO ORDER
not in any usable way. There *is* no "first record" or "thir
record"

If you have some field or fields within the table which will let yo
sort the data into a particular order, .......<snip>

You are using a table as the record source for the form (not sorted) and a table (not sorted) as the source for the SQL record source

Its there a field (a date field or ???) that could be used to sort both record sources so there IS a 'FIRST' and 'LAST' record

Next question
you wrote
when i run my code, if there is a record on the recordset, i put the values on memory variables (x1, x2, x3....) , then i past >them to my textboxes on my form, after i went to the last record (rs.MoveLast)
Click to expand...

You want to go to the "last" record of TPavimentos and update the fields OR are you creating a NEW record

Last question

If there are 5 records that meet the SQL recordset (from TLajes ), are you want only to use the "first" record or do you want to loop thru the 5 records putting the data from each record into the form? Or could there be more than 1 record? Maybe not??

===
What i really want the code do is
Click to expand...

I understand this part

Below is the modified code

'--- snip --
Dim db As DAO.Databas
Dim rst As DAO.Recordse
Dim varSQL As Strin

Set db = CurrentD

varSQL = "SELECT * FROM TLajes WHERE ((((((((TLajes.Vigota = " & Me.ALTVIGOTA & ")) AND (TLajes.Altura = " & Me.ALTPAVIM & ")) AND (TLajes.MSD <= TLajes.MRD)) AND (TLajes.VSD <= TLajes.VRD)) AND (TLajes.MSFCTK <= TLajes.MFCTK)) AND (TLajes.FSLEXA <= 1.5)) AND (TLajes.FSLEXA <= " & calvao & "));

Set rst = db.OpenRecordset(varSQL

'----- changed between her
If rst.BOF And rst.EOF the
' no records returned for varSQ
' close the current recordse
rst.Clos
' and open a new on
Set rst = db.OpenRecordset("Select * from TLajes"
rst.MoveLas
Els
' records found for varSQL - goto the first recor
rst.MoveFirs
End I
'----- and her

' get the values from the fields in the tabl
codigo = rst![Tipopavimento
xpp = rst!p
xmrd1 = rst!MR
xvrd1 = rst!VR
xmfctk1 = rst!MFCT
xmsfctk = rst!msfct
xei = rst!E
xsd = rst!S
xlarg = rst!Largur
xvig = rst!Vigot
xalt = rst!Altur
xarm = rst!Ardis
xmsd = rst!MS
xvsd = rst!VS
xflexa = rst!fslex
xhc = rst!H

' move the fields to the textboxes in the for
Me.TIPO = codig
Me.PPPAVIM = xp
Me.CARGASPERM = Me.PARDIVIS + Me.REVESTIM + Me.OUTROS + Me.PPPAVI
Me.MRD = xmrd
Me.VRD = xvrd
Me.MFCTK = xmfctk
Me.EI = xe
Me.ESFMSD = xms
Me.ESFMFCTK = xmsfct
Me.DEFLPRAZO = xflex
Me.ESFAPOIOSVSD = xvs
Me.RECOBRIMENTO = xh

' Clean u
rst.Clos
Set rst = Nothin
Set db = Nothin

'---- snip ---

You should use a query as the record source for the form and sort it by one or more fields

There needs to be "ORDER BY {fieldName}" with one or more fields added to the varSQL string
(note: replace {fieldName} with a field name from TLajes - without the braces{}

The same goes for the new line - need an 'Order By' clause
Set rst = db.OpenRecordset("Select * from TLajes"
Change it to
Set rst = db.OpenRecordset("Select * from TLajes ORDER BY theFieldName"
(changing theFieldName to a field from TLajes

Then you WILL have "First" and "Last" records...

waiting to hear back from you..
 
Hi there

1 Questio

TLajes is ordered by field TipoPavimento - PrimaryKe
TPavimentos is ordered by field PROJECTO - PrimaryKe

Both tables can be sorted by TipoPaviment

2 Questio

in order to prevent to show more records that meet the criteria, and show only the first one, i go to the last recor

3 Questio

i want to use the "first" record that match the selection. If there is any record that matches the selection, i wil
want to get the last record of table TLajes and put the fields in my form

As you say, normaly there are more records that match the criteria, but i just want only the first one.

if there is anyone, i want to display the last record.

is it possible to catch the last record before aplly the SQL criteria , keep it in memory variables, and then make the all thing i am doing ( aplly the SQL criteria ). Of course i will have again the problem when it doesn't found any record
But, i can make a test and if there is ANY match for the SQL criteria (flag =on), i just fire the flag and show the last record that i previous get

What do you think about this ??

I will wait your advice

until there.

Best regard

Zec
 
Zeca

I was away from the computer for a couple of days. If you haven't already solved your problem, I think the following code should do what you want

'--- snip --
Dim db As DAO.Databas
Dim rst As DAO.Recordse
Dim varSQL As Strin

Set db = CurrentD

varSQL = "SELECT * FROM TLajes WHERE ((((((((TLajes.Vigota = " & Me.ALTVIGOTA & ")) AND (TLajes.Altura = " & Me.ALTPAVIM & ")) AND (TLajes.MSD <= TLajes.MRD)) AND (TLajes.VSD <= TLajes.VRD)) AND (TLajes.MSFCTK <= TLajes.MFCTK)) AND (TLajes.FSLEXA <= 1.5)) AND (TLajes.FSLEXA <= " & calvao & ")) Order By TipoPavimento;

Set rst = db.OpenRecordset(varSQL

'----- changed between her
If rst.BOF And rst.EOF the
' no records returned for varSQ
' close the current recordse
rst.Clos
'and open a new one (added the Order by to be able to select the LAST RECORD
Set rst = db.OpenRecordset("Select * from TLajes Order By TipoPavimento"
rst.MoveLas
Els
' records found for varSQL - goto the first recor
rst.MoveFirs
End I
'----- and her

' get the values from the fields in the tabl
codigo = rst![Tipopavimento
xpp = rst!p
xmrd1 = rst!MR
xvrd1 = rst!VR
xmfctk1 = rst!MFCT
xmsfctk = rst!msfct
xei = rst!E
xsd = rst!S
xlarg = rst!Largur
xvig = rst!Vigot
xalt = rst!Altur
xarm = rst!Ardis
xmsd = rst!MS
xvsd = rst!VS
xflexa = rst!fslex
xhc = rst!H
' move the fields to the textboxes in the for
Me.TIPO = codig
Me.PPPAVIM = xp
Me.CARGASPERM = Me.PARDIVIS + Me.REVESTIM + Me.OUTROS + Me.PPPAVI
Me.MRD = xmrd
Me.VRD = xvrd
Me.MFCTK = xmfctk
Me.EI = xe
Me.ESFMSD = xms
Me.ESFMFCTK = xmsfct
Me.DEFLPRAZO = xflex
Me.ESFAPOIOSVSD = xvs
Me.RECOBRIMENTO = xh

' Clean u
rst.Clos
Set rst = Nothin
Set db = Nothin

'---- snip ---

Let me know how it goes, amigo....
 
Still Learnin

That's the better name i can founf for you, my frien

i have , and i don't really know, to thank you

Your code is like a swiis watch ----------- PERFECTIO

Thanks a lot for your help, support and friendshi

Best regard

I hope, next time you will be near by

Thanks agai

José Santo
(e-mail address removed)

P.S. keep in touch
 
Back
Top