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

DLookup

P: 77
Hi

i am having little trouble with my events code in Access..

I am using the following code to refer to the Availablilty field in the Add_Video table,

Private Sub Combo33_BeforeUpdate(Cancel As Integer)
If [Forms]![Add_Video]![Availability] = False Then
MsgBox "Video not available"

Else
End If

but as i run it i get the following msg..

"ms access cant find the form add video referred to in a macro expression or visual basic code
Mar 15 '07 #1
Share this Question
Share on Google+
24 Replies


missinglinq
Expert 2.5K+
P: 3,532
Expand|Select|Wrap|Line Numbers
  1. If [Forms]![Add_Video]![Availability] = False
refers to a control on the form Add_Video, not ot a field in a table named Add_Video. Do you, in fact, have a form named Add_Video?

If you have a control on your form named Availability try

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  2.   If Me.Availability] = False Then
  3.      MsgBox "Video not available"
  4.   End If
  5.  
Mar 15 '07 #2

P: 77
thanks for reply

i tried ur code but gor following msg..

syntax error..

my database is about a video rentals store..

i have a form called rentals, and in that when a customer chooses a video, the video might not be available, this info is in the video table under the field availability.. so what i am trying to do is create an event which will tell the user that a particular video is not available when they choose to rent a video..
Mar 15 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
Sorry, I accidentally left a bracket in when replacing your code with mine; that's what is throwing the error:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  2. If Me.Availability] = False Then
  3.   MsgBox "Video not available"
  4. End If
should be:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  2.  If Me.Availability = False Then
  3.   MsgBox "Video not available"
  4.  End If
Mar 15 '07 #4

P: 77
it is still incorrect
Mar 15 '07 #5

Rabbit
Expert Mod 10K+
P: 12,364
Unless this is a snippet of code, you need End Sub at the end.
Mar 15 '07 #6

P: 77
i have also tried that and get the another error msg..

"compile error"..

what i need to know is how you referece a field in another table and check its data type, and if its false then i want msg showing that...

plz need asap help
Mar 16 '07 #7

Rabbit
Expert Mod 10K+
P: 12,364
i have also tried that and get the another error msg..

"compile error"..

what i need to know is how you referece a field in another table and check its data type, and if its false then i want msg showing that...

plz need asap help
What? This sounds like a completely different question. If it is, it should be in a different thread.

The name of the field, Availability, sounds to me as if it's a numeric rather than boolean field. If numeric then you're looking for =0, if it's boolean then = False should work.
Mar 16 '07 #8

missinglinq
Expert 2.5K+
P: 3,532
Yeah, Left End Sub of when copying and pasting!

Unless this is a snippet of code, you need End Sub at the end.
Mar 16 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
i have a form called rentals, and in that when a customer chooses a video, the video might not be available, this info is in the video table under the field availability.. so what i am trying to do is create an event which will tell the user that a particular video is not available when they choose to rent a video..
It sounds like you're acutally looking for a DLookup. If the Video table is called Video and has a field called VideoName the code would be as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  2.    If DLookup("[Availability]", "Video", "[VideoName]=" & Me!Combo33) = False Then
  3.       MsgBox "Video not available"
  4.    End If
  5. End Sub
  6.  
Mary
Mar 16 '07 #10

P: 77
i have a table called Video and a field which i need to lookup is called availability.....
Mar 19 '07 #11

Rabbit
Expert Mod 10K+
P: 12,364
i have a table called Video and a field which i need to lookup is called availability.....
This tells us nothing we don't already know. Look over the posts again and respond to the points brought up.
Mar 19 '07 #12

P: 77
none of the above codes are working.. i tried all of them...
Mar 19 '07 #13

Rabbit
Expert Mod 10K+
P: 12,364
You say that you have a table called Video but your first post says Add_Video, which is it?

What is the name of video name or ID field? Is it a text or numeric field?
Mar 19 '07 #14

P: 77
the name of the table is "Video"... and the field is a yes/no field, the field i need to do a dlookup is for the "Availability" and thats in the "video" table..
Mar 20 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
the name of the table is "Video"... and the field is a yes/no field, the field i need to do a dlookup is for the "Availability" and thats in the "video" table..
Yes but what is the name of the field that would correspond to the value in the combo box. That is where I am using VideoName in the below code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  2.    If DLookup("[availability]", "Video", "[VideoName]=" & Me!Combo33) = False Then
  3.       MsgBox "Video not available"
  4.    End If
  5. End Sub
  6.  
Mar 20 '07 #16

Rabbit
Expert Mod 10K+
P: 12,364
I didn't ask about the Availability field. What is the the field that uniquely identifies a video? Is that text or numeric? What is it called?
Mar 20 '07 #17

P: 77
its called video ID and its a text field
Mar 21 '07 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
So try this code ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  2.    If DLookup("[availability]", "Video", "[Video ID]=" & Me!Combo33) = False Then
  3.       MsgBox "Video not available"
  4.    End If
  5. End Sub
  6.  
Mar 21 '07 #19

P: 77
So try this code ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  2.    If DLookup("[availability]", "Video", "[Video ID]=" & Me!Combo33) = False Then
  3.       MsgBox "Video not available"
  4.    End If
  5. End Sub
  6.  
i get the msg " you cancelled the previous operation"
Mar 22 '07 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
i get the msg " you cancelled the previous operation"
What is the datatype of the Availability field?
Mar 22 '07 #21

P: 77
What is the datatype of the Availability field?

it is a yes/no data type
Mar 22 '07 #22

Denburt
Expert 100+
P: 1,356
Mind if I...
According to post #18
its called video ID and its a text field
if this is correct and your comboBox is bound to a text field and all the other info you provided is accurate then this should do it.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  2.    If DLookup("[availability]", "Video", "[Video ID]='" & Me!Combo33 & "'") = False Then
  3.       MsgBox "Video not available"
  4.    End If
  5. End Sub
  6.  
Mar 23 '07 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Mind if I...
According to post #18


if this is correct and your comboBox is bound to a text field and all the other info you provided is accurate then this should do it.
Good catch.

Don't know how I missed that :rolleyes:
Mar 23 '07 #24

Denburt
Expert 100+
P: 1,356
Thanks np :)
Mar 23 '07 #25

Post your reply

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