Complex(for me) INSTR problem in MS Access

**Denburt** · May 2 '07, 03:38 PM

I think you are making good progress with your procedure however I would like to point out a couple of things that may help.

I know I copy and paste code however I am VERY careful to look it over and read everything I can to try and understand the concepts as should you. I know VBA may be new to you but not very often will you find a complete copy paste solution. You will find most developers will be glad to help step you through your problems and issues the biggest thing they want is for you to learn what you are doing and how it is done, even if someone gives you a copy paste solution it may not be designed for all of your needs or could have errors that can be easily remedied.

I am self taught so I understand that it may be a bit frustrating, but not even Microsoft gives complete copy paste solutions whether it is in a help file or otherwise. Something is almost ALWAYS missing or needs a slight adjustment.

For instance:

Code:

' test for rows to be processed, else stop
If rs.EOF And rs.BOF Then
   Exit Function
End If

rs is not declared or even initialized, this can generate an error, in fact this is the only place it is even used try changing this to a recordset you are fixing to try and loop through such as the "rsIn". There is a few things in the code that you may or may not plan to use so keep this in mind.

My next question is the table names I didn't see them when I browsed the posts and I have been Kinda watching this one. Is your table name actually "tblWorkOrderDe scription" as per nico's code or do you need to change these?

When you are in the VBA window you have a completely different set of help files. You can easily find out more about recordsets by highlighting a word then press F1 (on a pc).

On the top of my modules I also ALWAYS make sure the following is in place and I Compile/Save constantly:

Code:

Option Compare Database
Option Explicit

More about that here:
Writing Solid Script

To compile click on the debug menu then click compile.

I hope this helps some.

**RobertGabriel** · May 2 '07, 04:05 PM

[QUOTE=Denburt] I would like to point out a couple of things that may help.

Code:

' test for rows to be processed, else stop
If rs.EOF And rs.BOF Then
   Exit Function
End If

rs is not declared or even initialized, this can generate an error, in fact this is the only place it is even used try changing this to a recordset you are fixing to try and loop through such as the "rsIn". There is a few things in the code that you may or may not plan to use so keep this in mind./QUOTE]

Code:

Option Compare Database
Option Explicit

as soon as I added the Option explicit and compiled the error showed up.

Originally posted by Denburt

My next question is the table names I didn't see them when I browsed the posts and I have been Kinda watching this one. Is your table name actually "tblWorkOrderDe scription" as per nico's code or do you need to change these?

I can name the tables anything.
I appreciate all comments as I struggle with VB

**Denburt** · May 2 '07, 04:15 PM

You don't need to change the tables names just change the code to address the tables you have. Make the other corrections and let us know where things stand.

**RobertGabriel** · May 2 '07, 05:58 PM

Code:

Option Compare Database
Option Explicit

Function fncGetWorkDetail()
' The input table records
Dim rsIn As DAO.Recordset
' The output records with the details
Dim rsOut As DAO.Recordset
Dim strDescription As String
Dim strCrNum As String
Dim strNumber As String
Set rsIn = CurrentDb.OpenRecordset("tblWorkOrderDescription")
Set rsOut = CurrentDb.OpenRecordset("tblWorkDetail")
' test for rows to be processed, else stop
If rsIn.EOF And rsOut.BOF Then
   Exit Function
End If
rsIn.MoveFirst
While Not rsIn.EOF
strCrNum = rsIn!CRNum
strDescription = rsIn!Description
While InStr(strDescription, "200") > 0
    ' get string
    strNumber = Mid(strDescription, InStr(strDescription, "200"), 9)
    ' act on found string posistion...
    ' remove part with number (no length specified will take all from starting position)
    strDescription = Mid(strDescription, InStr(strDescription, "200") + 9)
Wend
    rsOut.AddNew
    strCrNum = rsOut!CRNum
    strNumber = rsOut!Number
    rsOut.Update
    rsIn.MoveNext
Wend
End Function

**nico5038** · May 2 '07, 06:06 PM

We're almost there, just place the code with the "rsOut.AddN ew" inside the while loop for the selection of the "200" codes as each code needs to be saved.

To check or all is working correct you can place a breakpoint (click in the left ruler besides the first SET statement and type in the immediate window at the bottom:
call fncGetWorkDetai l
after pressing [Enter] the code will start executing and halt on the breakpoint.
Now press F8 for single stepping through the code and inspect the values of the fields by hovering with your mousepointer above a field.

Nic;o)

**Denburt** · May 2 '07, 06:13 PM

?????????? You posted more code but didn't tell us anything are you still having a problem? Are you getting an error?

I am not trying to be sarcastic but someone could stumble in to try and help and they may not be able to pick out the issues as well as other more seasoned programmers.

Yes I can still see problems and yes you are getting errors

Originally posted by DenBurt

When you are in the VBA window you have a completely different set of help files. You can easily find out more about recordsets by highlighting a word then press F1 (on a pc).

I know the help files may not answer all of your questions for instance what the heck is a recordset (simply opening your table in code you won't see it but the code can).

If you are not sure about EOF or BOF try highlighting them and you will see the first bump I noticed.

**RobertGabriel** · May 2 '07, 07:04 PM

I am getting an illegal use of Null with this
at "strCrNum = rsOut!CRNum"

Code:

Option Compare Database
Option Explicit
Function fncGetWorkDetail()
' The input table records
Dim rsIn As DAO.Recordset
' The output records with the details
Dim rsOut As DAO.Recordset
Dim strDescription As String
Dim strCrNum As String
Dim strNumber As String


Set rsIn = CurrentDb.OpenRecordset("open_masterlist")
Set rsOut = CurrentDb.OpenRecordset("Master_List_Orders")



' test for rows to be processed, else stop
If rsIn.EOF And rsOut.BOF Then
   Exit Function
End If

rsIn.MoveFirst

While Not rsIn.EOF
strCrNum = rsIn!CRNumber
strDescription = rsIn!Comments
While InStr(strDescription, "200") > 0
    ' get string
    strNumber = Mid(strDescription, InStr(strDescription, "200"), 9)
    ' act on found string posistion...

    ' remove part with number (no length specified will take all from starting position)
    strDescription = Mid(strDescription, InStr(strDescription, "200") + 9)
    
    rsOut.AddNew
    strCrNum = rsOut!CRNum
    strNumber = rsOut!Number
    rsOut.Update

Wend
    
   rsIn.MoveNext
Wend

End Function

**nico5038** · May 2 '07, 07:11 PM

That's because the output field needs to be filled.....

Nic;o)

**Denburt** · May 2 '07, 07:41 PM

Much more informative this time however you still are not checking to make sure that rsIn is not EOF or BOF so that could be the reason...

From post #21

Originally posted by DenBurt

If you are not sure about EOF or BOF try highlighting them and you will see the first bump I noticed.

Before this line:

Code:

strCrNum = rsIn!CRNumber

Add a break point and a print statement, or message box and verify that rsIn!CRNumber contains data.

Code:

debug.print rsIn!CRNumber
strCrNum = rsIn!CRNumber

**RobertGabriel** · May 2 '07, 09:13 PM

Originally posted by Denburt

Much more informative this time however you still are not checking to make sure that rsIn is not EOF or BOF so that could be the reason...
From post #21
Before this line:

Code:

strCrNum = rsIn!CRNumber

Add a break point and a print statement, or message box and verify that rsIn!CRNumber contains data.

Code:

debug.print rsIn!CRNumber
strCrNum = rsIn!CRNumber

That debug code is cool . I believe my problem initially is the file I want to append to has no records. When I place the curser over the variables it tells me what is in them. When I get to :

Code:

    rsOut.AddNew
    strCrNum = rsOut!CRNum
    strNumber = rsOut!Number
    rsOut.Update

The "rsOut!CrNu m" says Null and the "strCrNum" has a value.

**Denburt** · May 2 '07, 10:04 PM

Slight adjustment needs to be made, your variable is carrying the info you want to add to the "rsOut" recordset so you want to tell the rsOut recordset to recieve the data like so:

Code:

rsOut.AddNew
      rsOut!CRNum=strCrNum
     rsOut!Number=strNumber 
    rsOut.Update

And that should get you a little further down the road, good luck keep us updated.

**RobertGabriel** · May 3 '07, 08:16 PM

Originally posted by Denburt

Slight adjustment needs to be made, your variable is carrying the info you want to add to the "rsOut" recordset so you want to tell the rsOut recordset to recieve the data like so:

Code:

rsOut.AddNew
      rsOut!CRNum=strCrNum
     rsOut!Number=strNumber 
    rsOut.Update

And that should get you a little further down the road, good luck keep us updated.

My "rsOut!CrNu m" still says Null and the "strCrNum" has a value.

**Denburt** · May 3 '07, 10:22 PM

Originally posted by RobertGabriel

My "rsOut!CrNu m" still says Null and the "strCrNum" has a value.

Thats sounds right, You are trying to add data to that table aren't you?

Did you try and run it as it is now? If you did what happened are you getting an error if so tell us about it let us know the error number any message that the error spits out and if you have the debug option click it and show us that yellow line of code plus a few lines above and below it.

Thanks happy to help.

**RobertGabriel** · May 4 '07, 02:10 PM

This is working except when I get to a record that has null in the comments from rsIn, I get an error here: "line 28"
strDescription = rsIn!Comments
Runtime error 94 "invalid use of null"
rsIn!Comments is null at this point.

Code:

Option Compare Database
Option Explicit
Function fncGetWorkDetail()
' The input table records
Dim rsIn As Recordset
' The output records with the details
Dim rsOut As Recordset
Dim strDescription As String
Dim strCrNum As String
Dim strNumber As String

Set rsIn = CurrentDb.OpenRecordset("open_masterlist")
Set rsOut = CurrentDb.OpenRecordset("Master_List_Orders")
' test for rows to be processed, else stop
If rsIn.EOF Then
   Exit Function
End If
rsIn.MoveFirst
While Not rsIn.EOF
'Debug.Print rsIn!CRNumber
If rsIn!CRNumber = Null Then
   rsIn.MoveNext
End If
strCrNum = rsIn!CRNumber
If rsIn!Comments = Null Then
    rsIn.MoveNext
End If
strDescription = rsIn!Comments
While InStr(strDescription, "200") > 0
    ' get string
    Debug.Print strDescription
    strNumber = Mid(strDescription, InStr(strDescription, "200"), 9)
    ' act on found string posistion...
    ' remove part with number (no length specified will take all from starting position)
    strDescription = Mid(strDescription, InStr(strDescription, "200") + 9)
    Debug.Print strDescription
    rsOut.AddNew
    rsOut!CRNum = strCrNum
    rsOut!Number = strNumber
    rsOut.Update
Wend
   rsIn.MoveNext
Wend
End Function

**nico5038** · May 4 '07, 07:13 PM

Try:

strDescription = NZ(rsIn!Comment s)

This will "neutralize " the effect of the Nulls.

Nic;o)