How would the Pro's do this?

[Minty]

You can't do much at all without DOA or ADO if you want to use Access and recordsets. This sound more like some another problem.

Could you upload a zipped stripped down version of your database, just a few records - enough to be able to see the form and make it work?

 

[JJSHEP89]

see attached. Hopefully i didnt strip it down too much. the ToolBook form is the main user interface where they will enter in the tool number into the search bar at the top right corner. the idea is that if there is a critical message associated with that tool, it will then pop up. the critical message form is just the pop up.

 

[TJPoorman]

Since your ToolNumber is text you need single quotes around it.
Like so:

Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varTN As Variant

varTN = InputBox("Enter a Tool Number or Part Number.")

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ToolingNotes WHERE TN_ToolNumber = [COLOR="Red"]'[/COLOR]" & varTN & "[COLOR="red"]'[/COLOR] AND TN_Critical = TRUE", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
    DoCmd.OpenForm "CriticalMessage", , , "TN_ToolNumber =[COLOR="red"]'[/COLOR]" & varTN [COLOR="red"]& "'"[/COLOR], , acDialog
    
    rs.MoveNext
    db.Close
    Set db = Nothing
    
End Sub

 

[TJPoorman]

I also notice you make a reference to TN_ToolNumber, but this is not a field in your table.
It looks like you need [Tool Number]. Be sure to use the brackets since your field name has a space in it.

 

[JJSHEP89]

I also notice you make a reference to TN_ToolNumber, but this is not a field in your table.
It looks like you need [Tool Number]. Be sure to use the brackets since your field name has a space in it.

it may have been deleted on the stripped DB. not sure why though, TN_ToolNumber is the tool number field in the ToolingNotes table.

 

[gemma-the-husky]

out of interest, what do you want then to do with the "critical" messages?

I recall the old original "hitchhikers guide to the galaxy", where at the start there was an option that the game kept trying to get you to disregard, and you had to request the same action about 5 times. That sort of thing would probably stop them "simply" ignoring the error.

Alternatively, less tongue in cheek, make them enter something some where to confirm they had read it, so they can't just close the msg box on "auto"

Again, alternatively, make it a management function to read the "critical" comments, and do something about them.

 

[MarkK]

I would expect the code to look something like...

Private Sub Test9146710()
    Const SQL As String = _
        "SELECT NoteID FROM ToolingNotes " & _
        "WHERE TN_ToolNumber = '?' AND TN_Critical = TRUE"
    
    Dim rsp As String
    rsp = InputBox("Enter a Tool Number or Part Number.")
    
    If Len(rsp) Then
        With CurrentDb.OpenRecordset(Replace(SQL, "?", rsp))
            Do While Not .EOF
          [COLOR="Green"]      'show message for this NoteID --  somehow[/COLOR]
                .MoveNext
            Loop
            .Close
        End With
    End If

End Sub

For the code you posted--and in your db I downloaded too--you are getting a "Do without Loop" compile error, not a "User defined type not defined."

 

[JJSHEP89]

I would expect the code to look something like...

Private Sub Test9146710()
    Const SQL As String = _
        "SELECT NoteID FROM ToolingNotes " & _
        "WHERE TN_ToolNumber = '?' AND TN_Critical = TRUE"
    
    Dim rsp As String
    rsp = InputBox("Enter a Tool Number or Part Number.")
    
    If Len(rsp) Then
        With CurrentDb.OpenRecordset(Replace(SQL, "?", rsp))
            Do While Not .EOF
          [COLOR="Green"]      'show message for this NoteID --  somehow[/COLOR]
                .MoveNext
            Loop
            .Close
        End With
    End If

End Sub

For the code you posted--and in your db I downloaded too--you are getting a "Do without Loop" compile error, not a "User defined type not defined."

running this code im getting a run-time error '3061' too few parameters. expected 1.

this is on the OpenRecordset line. I've tried changing the constant to a Dim and still the same thing....

 

[MarkK]

Does the SQL use correct field names? Is NoteID a field in your table? This code is not a turn-key solution, it demonstrates one possible way to address your loop, but there is still the fairly difficult problem of the how to open multiple forms.

Maybe it would be better if you display these notes in an view that can display multiple rows at once?

 

[TJPoorman]

This works for me in your stripped database:

Dim rcrdst As DAO.Recordset
Dim varTN As Variant

varTN = InputBox("Enter a Tool Number or Part Number.")

Set rcrdst = CurrentDb.OpenRecordset("SELECT * FROM ToolingNotes WHERE TN_ToolNumber = '" & varTN & "' AND TN_Critical = TRUE", dbOpenDynaset, dbSeeChanges)

Do While Not rcrdst.EOF
    DoCmd.OpenForm "CriticalMessage", , , "TN_ID = " & rcrdst!TN_ID, , acDialog
    
    rcrdst.MoveNext
Loop

One problem you have is with your pop-up form. You have it set to dataentry which will not show any records.

 

[JJSHEP89]

Does the SQL use correct field names? Is NoteID a field in your table? This code is not a turn-key solution, it demonstrates one possible way to address your loop, but there is still the fairly difficult problem of the how to open multiple forms.

Maybe it would be better if you display these notes in an view that can display multiple rows at once?

I modified it to fit my field names etc. here's the exact code, copy pasted

Sub MessageSearch()
    Dim strToolNumber As String
    Const strSQL As String = _
        "SELECT ToolingNotes.TN_Note, ToolingNotes.TN_PartNumber " & _
        "FROM ToolingNotes " & _
        "WHERE ToolingNote.TN_ToolNumber = '?' AND ToolingNotes.TN_Critical = True;"

    strToolNumber = InputBox("Please enter a tool number.", "Tool Number", Default)
    
    If Len(strToolNumber) Then
        With CurrentDb.OpenRecordset(Replace(strSQL, "?", strToolNumber))
            Do While Not .EOF
                DoCmd.OpenForm "CriticalMessage", , , "TN_ToolNumber ='" & strToolNumber & "'", , acDialog
                .MoveNext
            Loop
            .Close
        End With
    End If
    
End Sub

 

[Minty]

Your code doesn't include the changing note number just the tool number so the recordset isn't doing anything.

TJPoormans code loops around the Note Id's and works.

 

[MarkK]

OK, and this code is working for you? What happens when there are multiple notes for the same tool? That is the whole reason for the loop, but that method of opening the form, as far as I can tell, will only show a single note. So if the form can only show a single record, we've defeated the purpose of the loop.

To open multiple instances of the same form to show different data, use "access non-default form instance" as your search terms.

Alternatively, use a form that displays multiple rows like a datasheet, continuous form, or use a form that has a listbox on it. These are a few ways you can display multiple records at once, which is what I understand you mean to do.

hth
Mark

 

[MarkK]

And Minty is correct, you have not used the data from the recordset.

 

[JJSHEP89]

OK, and this code is working for you? What happens when there are multiple notes for the same tool?
hth
Mark

so the code listed above was not working for me. i tried a few different things with it and couldnt get it to function properly.

Your code doesn't include the changing note number just the tool number so the recordset isn't doing anything.

TJPoormans code loops around the Note Id's and works.

AHHH, yes i keep trying to loop around the tool number... yes, thank you!

However, Im still getting the same compile error as before with TJPoorman's code, :banghead: here it is copy pasted....

Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim varTN As Variant

varTN = InputBox("Enter a Tool Number or Part Number.")

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ToolingNotes WHERE TN_ToolNumber = '" & varTN & "' AND TN_Critical = TRUE", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
    DoCmd.OpenForm "CriticalMessage", , , "TN_ID = " & rs!TN_ID, , acDialog
    
    rs.MoveNext
Loop
    
End Sub

So when i try and open the form using this exact code i still get the compile error, but when i go into the debugger and stop running the code the form pops up with the proper text. (sorry for switching the recordset variable around so much, i've been playing around with a few different things in my code)

 

[TJPoorman]

Where are you putting this code? The Form_Current of which form? Your popup?
If so, that is why there is an error. This should be on the Form_Current of your tool list.

 

[MarkK]

...using this exact code i still get the compile error...

What is the error description? What line causes the error?

 

[JJSHEP89]

Where are you putting this code? The Form_Current of which form? Your popup?
If so, that is why there is an error. This should be on the Form_Current of your tool list.

its on the form_current of the main form (user interface), it was on the pop-up prior (on the stripped DB) but i fixed that.

What is the error description? What line causes the error?

the same error as before, Compile error: User-Defined type not defined. The dim rs As DAO.Recordset line is highlighted.

 

[JJSHEP89]

solved. the complie error was coming from a missing library reference. not only do you need the Microsoft Access 15.0 Object Library but you also need the Microsoft DAO 3.6 Object Library. once i added the other library it worked flawlessly. (this is for access 2013, not sure what the other versions would need) Thanks again to everyone for your help, i appreciate the patience yall had for this novice. here is the final code i used, the varToolNumber is a public variable i have defined in another function.

Private Sub Form_Current()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM ToolingNotes WHERE TN_ToolNumber = '" & varToolNumber & "' AND TN_Critical = TRUE", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
    DoCmd.OpenForm "CriticalMessage", , , "TN_ID = " & rs!TN_ID, , acDialog
    rs.MoveNext
Loop
    
End Sub

 

[MarkK]

OK, so the problem now is that inside your loop you open the form, but once it's open, and the initial filter is applied, it won't open again or apply another filter if there are multiple iterations to the loop.