lookup box problem

[shonam]

Hello
I haven't done much Access work for a while and so am a bit rusty. Having a problem and hope someone can help.
I have a look-up (combo) box on a form displaying course details. The form shows courses for a specific agency - the agency is filtered as that shown on a previous form.

When using the look-up box, if a course is selected that doesn't exist for the specific agency then nothing happens - I'd really like to display a message box that says "no course available" or something - but can't figure out where to put the message box code or how to call it

so far I have

Private Sub Combo23_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[course name] = '" & Me![Combo23] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

The closest I've got to making anything happen was by putting
If rstRecordSet.EOF = False Then MsgBox "No course of this name listed for this agency" before the End Sub - but I get a run-time error which says "run-time error 424 object required"

totally out of my depth - any help appreciated

thanks
Shona

[Stwange]

Try this, it should work but I haven't tested it:

Private Sub Combo23_AfterUpdate()
    IF DCount("*","YourTableName",[course name] = '" & Me![Combo23] & "') <> 0 then 
    ' Find the record that matches the control.
        Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[course name] = '" & Me![Combo23] & "'"
    Else
        msgbox "No course of this name listed for this agency", vbcritical, "Course not found."
    End If
End Sub
 

Hope this helps.

[shonam]

I replaced "your table name" with "courses" - it didn't like the second " so I took it off - but afraid it still doesn't call the message box

the code I have is:

Private Sub Combo23_AfterUpdate()
If DCount("*", "courses,[course name] = '" & Me![Combo23] & "'") <> 0 Then
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[course name] = '" & Me![Combo23] & "'"
Else
MsgBox "No course of this name listed for this agency", vbCritical, "Course not found."
End If
End Sub

and this returns a run-time error: 3078 - the Microsoft Jet database engine cannot find the input table or query 'courses,[course name] = "name of the course I was looking for". Make sure it exists and that its name is spelled correctly.

many thanks
Shona

[Stwange]

Try replacing it with this instead:

IF DCount("*","courses",[course name] = '" & Me![Combo23] & "'")
 

(I missed out a " sorry, it should have had one added, not deleted)

[shonam]

thanks again - am now getting the line that starts "If Dcount ..." coming up in red and at the part [course name] = ' " the ' is highlighted and it says compile error expected expression.
sorry to be a pest

best wishes
Shona

[Stwange]

sorry to be a pest

Not at all, if anything it's my fault for giving you bad code...twice. I missed out a " again! try this one:

If DCount("*", "courses,"[course name] = '" & Me![Combo23] & "'") <> 0 Then
 

Good luck :)

[shonam]

Fraid not - the line is red and I get compile error - syntax error when I access the combo box
thanks again
Shona

[Stwange]

Fraid not - the line is red and I get compile error - syntax error when I access the combo box
thanks again
Shona

Please can you paste/rewrite the syntax error? This is getting embarrassing :)

[shonam]

That's all it says:

I click on a course in the combo box that doesn't exist for the selected agency - the screen flicks to the Visual Basic screen with the DCount row highlighted and a grey pop-up box that says :

Compile error:
Syntax error

with the yellow triangle and ! sign but no other detail

I'm really sorry
Shona

[Stwange]

That's all it says:

I click on a course in the combo box that doesn't exist for the selected agency - the screen flicks to the Visual Basic screen with the DCount row highlighted and a grey pop-up box that says :

Compile error:
Syntax error

with the yellow triangle and ! sign but no other detail

I'm really sorry
Shona

I think I've seen the problem, and I swear I'm going to get it right this time:
If DCount("*", "courses","[course name] = '" & Me![Combo23] & "'") <> 0 Then

Let me know, and good luck :)

[shonam]

please don't hate me but -

compile error: expected: =

the <> is highlighted

[shonam]

IGNORE THAT - missed the If - trying again

[shonam]

well - the good news is that there isn't an error message but I'm afraid the message box doesn't appear

maybe it's just not meant to be

Have to dash off for the school run now but many thanks for all your patience and help

Shona

[Stwange]

well - the good news is that there isn't an error message but I'm afraid the message box doesn't appear

maybe it's just not meant to be

Have to dash off for the school run now but many thanks for all your patience and help

Shona

No problem. I'm leaving work now anyway, I'll see if I get chance to have another look either tonight or tomorrow. If anyone else can suggest a problem in my coding, it would be helpful :)

[shonam]

Good morning - took my old Access VB book home for a bit of light reading and came up with

Private Sub Combo23_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[course name] = '" & Me![Combo23] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

If [course name].Value <> Me![Combo23] Then
MsgBox "No course of this name listed for this agency"
End If

End Sub

and it seems to work - hurrah!!

Many, many thanks for your help yesterday

Shona

[Stwange]

Many, many thanks for your help yesterday

Shona

No problem, and sorry I wasn't successful!