RE: New to VBA, Search for hole in numeric value using string as a cri

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


"thspimpolds@xxxxxxxxx" wrote:

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

Relevant Pages

  • Re: Open Embeded Word files and save them out
    ... a BoundObjectFrame control bound to the OLE field. ... Loop through a table of embeded Word docs ... > Dim rst As New ADODB.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Hi, flickering PDA screen with vb2005 in vs 2005
    ... I have created a form which is I am filling from my programe in a loop ... Dim control As Control ...
    (microsoft.public.pocketpc.developer)
  • Re: Really easy question about looping through controls
    ... I want to loop through those nine textboxes, ... dim arMatrixas Byte ... Create an array with the control references in it: ... Dim aTextbox(8) As Textbox ...
    (microsoft.public.dotnet.languages.vb.controls)
  • RE: accesing controls in for each loop
    ... I suggest you don't use a For Each loop but a For i = x to y type instead: ... Dim i As Integer ... > o is the current control being 'looked' at out of the X number of controls ... Prev by Date: ...
    (microsoft.public.excel.programming)
  • Re: IE sequencing and cookie notification popups
    ... ' 10Apr04: accept cookies from hotmail. ... Dim SetWindowLong ' as object ... Const sDlgCaption = " Kookie Kruncher, ... ' notifications associated with that type of control. ...
    (microsoft.public.scripting.vbscript)