DLookup

[Mubs]

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

[missinglinq]

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

 Private Sub Combo33_BeforeUpdate(Cancel As Integer)
  If Me.Availability] = False Then
     MsgBox "Video not available"
  End If
 

[Mubs]

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..

[missinglinq]

Sorry, I accidentally left a bracket in when replacing your code with mine; that's what is throwing the error:

Private Sub Combo33_BeforeUpdate(Cancel As Integer)
If Me.Availability] = False Then
  MsgBox "Video not available"
End If

should be:

Private Sub Combo33_BeforeUpdate(Cancel As Integer)
 If Me.Availability = False Then
  MsgBox "Video not available"
 End If

[Mubs]

it is still incorrect

[Rabbit]

Unless this is a snippet of code, you need End Sub at the end.

[Mubs]

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

[Rabbit]

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.

[missinglinq]

Yeah, Left End Sub of when copying and pasting!

Unless this is a snippet of code, you need End Sub at the end.

[MMcCarthy]

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:

Private Sub Combo33_BeforeUpdate(Cancel As Integer)
   If DLookup("[Availability]", "Video", "[VideoName]=" & Me!Combo33) = False Then
      MsgBox "Video not available"
   End If
End Sub
 

Mary

[Mubs]

i have a table called Video and a field which i need to lookup is called availability.....

[Rabbit]

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.

[Mubs]

none of the above codes are working.. i tried all of them...

[Rabbit]

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?

[Mubs]

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..

[MMcCarthy]

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

Private Sub Combo33_BeforeUpdate(Cancel As Integer)
   If DLookup("[availability]", "Video", "[VideoName]=" & Me!Combo33) = False Then
      MsgBox "Video not available"
   End If
End Sub
 

[Rabbit]

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?

[Mubs]

its called video ID and its a text field

[MMcCarthy]

So try this code ...

Private Sub Combo33_BeforeUpdate(Cancel As Integer)
   If DLookup("[availability]", "Video", "[Video ID]=" & Me!Combo33) = False Then
      MsgBox "Video not available"
   End If
End Sub
 

[Mubs]

So try this code ...

Private Sub Combo33_BeforeUpdate(Cancel As Integer)
   If DLookup("[availability]", "Video", "[Video ID]=" & Me!Combo33) = False Then
      MsgBox "Video not available"
   End If
End Sub
 

i get the msg " you cancelled the previous operation"

[MMcCarthy]

i get the msg " you cancelled the previous operation"

What is the datatype of the Availability field?

[Mubs]

What is the datatype of the Availability field?

it is a yes/no data type

[Denburt]

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.

Private Sub Combo33_BeforeUpdate(Cancel As Integer)
   If DLookup("[availability]", "Video", "[Video ID]='" & Me!Combo33 & "'") = False Then
      MsgBox "Video not available"
   End If
End Sub
 

[MMcCarthy]

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:

[Denburt]

Thanks np :)