By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,485 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

lookup box problem

P: 19
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
Sep 5 '07 #1
Share this Question
Share on Google+
15 Replies


Expert 100+
P: 126
Try this, it should work but I haven't tested it:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo23_AfterUpdate()
  2.     IF DCount("*","YourTableName",[course name] = '" & Me![Combo23] & "') <> 0 then 
  3.     ' Find the record that matches the control.
  4.         Dim rs As Object
  5.         Set rs = Me.Recordset.Clone
  6.         rs.FindFirst "[course name] = '" & Me![Combo23] & "'"
  7.     Else
  8.         msgbox "No course of this name listed for this agency", vbcritical, "Course not found."
  9.     End If
  10. End Sub
  11.  
Hope this helps.
Sep 5 '07 #2

P: 19
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
Sep 5 '07 #3

Expert 100+
P: 126
Try replacing it with this instead:
Expand|Select|Wrap|Line Numbers
  1. IF DCount("*","courses",[course name] = '" & Me![Combo23] & "'")
  2.  
(I missed out a " sorry, it should have had one added, not deleted)
Sep 5 '07 #4

P: 19
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
Sep 5 '07 #5

Expert 100+
P: 126
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:
Expand|Select|Wrap|Line Numbers
  1. If DCount("*", "courses,"[course name] = '" & Me![Combo23] & "'") <> 0 Then
  2.  
Good luck :)
Sep 5 '07 #6

P: 19
Fraid not - the line is red and I get compile error - syntax error when I access the combo box
thanks again
Shona
Sep 5 '07 #7

Expert 100+
P: 126
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 :)
Sep 5 '07 #8

P: 19
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
Sep 5 '07 #9

Expert 100+
P: 126
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 :)
Sep 5 '07 #10

P: 19
please don't hate me but -

compile error: expected: =

the <> is highlighted
Sep 5 '07 #11

P: 19
IGNORE THAT - missed the If - trying again
Sep 5 '07 #12

P: 19
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
Sep 5 '07 #13

Expert 100+
P: 126
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 :)
Sep 5 '07 #14

P: 19
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
Sep 6 '07 #15

Expert 100+
P: 126

Many, many thanks for your help yesterday

Shona
No problem, and sorry I wasn't successful!
Sep 6 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.