New to VBA, Search for hole in numeric value using string as a criteria

T

thspimpolds

I am new to VBA and Access 03. I know java, but it does not seem to
help much for VBA. I use a database for a local store and we keep our
inventory in Access 03 per our boss. I am trying to create some code
to do a serach. What the code needs to do is take a category name of
the item from the form. Use that as a criteria for searching the
database. Next once it knows what category to look at it needs to look
at a numeric field. This field while mainly sequential has holes in is
and is not all in one grouping. I need this function to find a hole in
the scale of what i will refer to as TCR numbers. Once it has found a
hole, while in the same category it needs to display that number in a
form/msg box.

Problems I ran into:
1. I cannot get the code to enter the do while loop
2. I cannot get the form to display anything but an object, not sure if
there is a dialog box method.
3. Cannot get the search to perform right.
4. Had an infinite loop, but I believe I fixed it.

Definition of variables:

rs - current recordset object
rs2 - record set of object to put new TCR number after it was found
into a seperate table(not needed if there is a message box method, just
a workaround i tried)
tcrNum - the number which is found by the search
tcrPull - the TCR number which is pulled from the form



I apologzie ahead of time, the code might be downright awful in syntax,
approach, and coding standards.



Code:

Private Sub Search_By_Item_Number_Click()

Dim rs As Object
Dim tcrNum As Integer
Dim catName As String
Dim tcrPull As Integer
Dim db As DAO.Database
Dim rs2 As DAO.Recordset
Dim control As Boolean


Set db = CurrentDb
Set rs2 = db.OpenRecordset("tbl_TCR_Next_Number")
Set rs = Me.Recordset.clone

catName = Me.Category
tcrPull = Me.TCR_Number
control = True





Do While control = True

rs.FindFirst [Category] = catName
tcrPull = 9999

If (tcrPull <> 0) Then
tcrNum = tcrPull


Exit Do

rs.FindNext [Category] = catName


Else: Exit Do


End If

Loop

tcrNum = (tcrNum + 1)

rs.MoveNext

If (rs("TCR_Number") <> tcrNum) Then
DoCmd.OpenForm "frm_Next_TCR_Number"
' nextTCRNum (tcrNum)

End If



End Sub
 
G

Guest

I'm not a real expert but being it is Sunday I'll try and help as much as I
can.


First, You are setting the control to true and never checking for it's
status again, I would suspect that is your infinate loop.

I would use a SQL Statement (or Query) for your recordsource so it only
returns the records you really want. Just those with the catagory of the
current record. Then loop through those looking for the gap in numbers.


Set rs = db.OpenRecordset("SELECT * FROM tbl_TCR_Next_Number WHERE
[Category] >= " & Me.Category, dbOpenSnapshot)

****syntax might need tweeking****

then I would use this to loop through records.
While Not rs.EOF

run throug the code
update recordset here
rs.MoveNnext

WEND

also might need to update recordset here for last record. I have found
that you will leave the While loop with the variables populated with the last
records data.



The last thing that might be of use is using the Locals Window to debug. In
case you are not familiar with this I'll eplain how to use it. While you are
in the VBA code module over on the left vertical bar you can click on a spot
in the code where you want to begin monitoring it while it is running. I
would put it on the while line. Then when you open the form and click on
whatever triggers the code you will be brought into the code and can step
through with the F8 key and watch the variables and looping. Go to View and
select Locals Window. you can also see the variables by mousing over them. I
hope this helps, I'll be here most of the day so post back if there is
anything else I can help you with. Good luck!!


[email protected] said:
I am new to VBA and Access 03. I know java, but it does not seem to
help much for VBA. I use a database for a local store and we keep our
inventory in Access 03 per our boss. I am trying to create some code
to do a serach. What the code needs to do is take a category name of
the item from the form. Use that as a criteria for searching the
database. Next once it knows what category to look at it needs to look
at a numeric field. This field while mainly sequential has holes in is
and is not all in one grouping. I need this function to find a hole in
the scale of what i will refer to as TCR numbers. Once it has found a
hole, while in the same category it needs to display that number in a
form/msg box.

Problems I ran into:
1. I cannot get the code to enter the do while loop
2. I cannot get the form to display anything but an object, not sure if
there is a dialog box method.
3. Cannot get the search to perform right.
4. Had an infinite loop, but I believe I fixed it.

Definition of variables:

rs - current recordset object
rs2 - record set of object to put new TCR number after it was found
into a seperate table(not needed if there is a message box method, just
a workaround i tried)
tcrNum - the number which is found by the search
tcrPull - the TCR number which is pulled from the form



I apologzie ahead of time, the code might be downright awful in syntax,
approach, and coding standards.



Code:

Private Sub Search_By_Item_Number_Click()

Dim rs As Object
Dim tcrNum As Integer
Dim catName As String
Dim tcrPull As Integer
Dim db As DAO.Database
Dim rs2 As DAO.Recordset
Dim control As Boolean


Set db = CurrentDb
Set rs2 = db.OpenRecordset("tbl_TCR_Next_Number")
Set rs = Me.Recordset.clone

catName = Me.Category
tcrPull = Me.TCR_Number
control = True





Do While control = True

rs.FindFirst [Category] = catName
tcrPull = 9999

If (tcrPull <> 0) Then
tcrNum = tcrPull


Exit Do

rs.FindNext [Category] = catName


Else: Exit Do


End If

Loop

tcrNum = (tcrNum + 1)

rs.MoveNext

If (rs("TCR_Number") <> tcrNum) Then
DoCmd.OpenForm "frm_Next_TCR_Number"
' nextTCRNum (tcrNum)

End If



End Sub
Click to expand...
 
A

Albert D.Kallal

Hum...it is not quite clear how you need/want to process the 2nd table of
tbl_TCR_Next_Number

Howeer, here is a basic code loop that will process each reocrd in the main
table with your given catagory...



Dim strSql As String
Dim rs As dao.Recordset
Dim rs2 As dao.Recordset

strSql = "select * from tblMain where Catagory = '" & Me.Catagory & "'"

Set rs = CurrentDb.OpenRecordset(strSql)
Set rs2 = CurrentDb.OpenRecordset("tbl_TCR_Next_Number")

Do While rs.EOF = False

' any record here is going to have the catagory we are
' looking for.

' do whatever

rs.MoveNext
Loop

rs.Close
rs2.Close

The above gives you an idea as to how to process the main reocrds. It is
just not clear how/what you want to do with the "next number" table here...
 
J

JohnGriffiths

Big question, WHY ?

If the category name is for people to read and understand,
and the category ID is for the db to understand, everything is working
normally.

If a category has been deleted and inventry items are still listed under
that ID and therefore you want to list all items that do not have a current
category ID.

SELECT inv.*
FROM Inventry inv LEFT JOIN Category cat ON inv.Category_ID = cat.ID
WHERE cat.ID IS NULL ;

Is this on the right lines - John

I am new to VBA and Access 03. I know java, but it does not seem to
help much for VBA. I use a database for a local store and we keep our
inventory in Access 03 per our boss. I am trying to create some code
to do a serach. What the code needs to do is take a category name of
the item from the form. Use that as a criteria for searching the
database. Next once it knows what category to look at it needs to look
at a numeric field. This field while mainly sequential has holes in is
and is not all in one grouping. I need this function to find a hole in
the scale of what i will refer to as TCR numbers. Once it has found a
hole, while in the same category it needs to display that number in a
form/msg box.
Click to expand...
<snip/>
 
T

thspimpolds

TCR number is not a category number, it is a identifying number in the
register program which is required for it to recognize the barcode.
Its technically the primary key in the oracle based program.

I think the SQL will be the best way to go about this. Thanks all,
hopefully this will work, or you very welll might here from me again.
 
T

thspimpolds

I have tried this but i cannot seem to have it find any null value.
The first problem is that i need to set all records in order by TCR
number. Then I need the code to look from the lowest number and find
the first null value in this series.

Example:

5100
5101
5102
5103
5105


I want the code to display 5104 or even set the TCR number field equal
to that value of the current record. I hope that clarifies my problem.

Update: I got it to select the correct records but i cant get it to
proccess the values. Keep in mind that the current record will have a
null tcr number, that is the number i am trying to find.
 
T

thspimpolds

I have tried this but i cannot seem to have it find any null value.
The first problem is that i need to set all records in order by TCR
number. Then I need the code to look from the lowest number and find
the first null value in this series.

Example:

5100
5101
5102
5103
5105


I want the code to display 5104 or even set the TCR number field equal
to that value of the current record. I hope that clarifies my problem.

Update: I got it to select the correct records but i cant get it to
proccess the values. Keep in mind that the current record will have a
null tcr number, that is the number i am trying to find.
 
T

thspimpolds

While that is EXACTLY what i need. For certian reasons I cannot make
the TCR Number a primary key becasue we require some duplicates. Is
there a way to perform that without making it a primary key?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trouble aborting a loop 5
VBA: How can I use a string variable as autofilter criteria? 4
Need expert coding help with a debugging issue. 12
Posting a dataset to a SQL table using VBA 0
Run-time error 430: Class does not support Automation..... 4
VBA to assign category on calendar item does not work ?? why ?? 5
VBA for NotInList return to old record or BeforeUpdate value 2
Run time error in VBA Code 1

Top