If ID is in table show....

[iProRyan]

Hi Guys,



Could do with a little help to finalize a project I am working on. I have a DVD Rental Database, and what I want to do is on the DVDs Form (Where all DVD details are entered) I want it show whether the DVD is in stock or if it is currently rented out. I presume I would have to say to the program something like this.

In Stock?(CheckBox) = Yes If DVD ID is in Rental Table Else No

Hope I have explained this ok.

Thanks,

Ryan

[Robeen]

You can try something like this DLookup:

If IsNull(DLookup("DVD_ID", "tblDVDs", "DVD_ID = '" & DVDID & "'")) Then
chkBox_InStock = False
Else
chkBox_InStock = True
End If

You'll have to modify the above code statement to match your field names etc . . . in the Table and the Form.
Let us know if you have problems with this.
All the best!

[iProRyan]

Hi,

Thanks for your help, just wondering where I would put this line of code, because it would change with every record (every DVD) would I put it in the Forms After Update or the check boxes after update, or somewhere different?

Thanks,

Ryan

[iProRyan]

Hello, this is what I have in the Form_Load at the moment - anything in square brackets isn't actually part of the code.

If IsNull(DLookup("DVDsID"[The field name of DVD], "Rentals"[Rentals table because that's where we want to know if it's in or not], "DVDsID=" & DVDID[The text box on form where dvd id is entered])) Then
StockTick [name of checkbox]= False
Else
StockTick = True
End If

or without square brackets

If IsNull(DLookup("DVDsID", "Rentals", "DVDsID=" & DVDID)) Then
StockTick = False
Else
StockTick = True
End If

This gives me a "Run-time error '3464': Data type mismatch in criteria expression"

Any ideas?

Thanks, Ryan

[Robeen]

In this case, the Run-time error actually tells you what the problem is.
The two different fields in your criteria are not of the same data type - so Access cannot evaluate the = sign that is between them because it would be like comparing apples to oranges.
In your DLookup() function:

If IsNull(DLookup("DVDsID", "Rentals", "DVDsID=" & DVDID)) Then

. . . where are you getting the 'Value' that is in 'DVDID'?
Before the snippet that you posted . . . is there a line of code in which you assign the value that is in the DVDID text box to 'DVDID'?

For example, do you have code that says something like:

Dim DVDID as Integer    '[or 'as String' if DVDsID in your Rentals table is defined as Text].
DVDID = Me.txtDVDID     '[where txtDVDID is the name of the text box on your Form].

?
The second line would assign the value that is in the text box to the variable 'DVDID'

You could try putting the code in the 'On Current' Event of the Form - so that each time there is a new record on the Form - the code will fire off & deal with teh check box.

Something is nagging at the back of my mind, though.
I feel like if I had designed this, I would just have a 'Boolean' field in the Rentals table that is True if the DVD is in stock - or False if it is rented out.
Then - you wouldn't need any code on the Form - your check box on the Form will automatically be checked if that field is True in the table and not-checked if that field is False.

I hope this helps.

[iProRyan]

Hi,

The DVDID that we want to know whether it is in the rentals table, is found from a text box on the form, which is unique to each record. The value of the text box stays the same for each record and when creating a new record it is calculated by a line of VBA code which says DVDID.Text = Total Number of Records in DVDs table + 1 (I couldn't use an AutoNumber because it messes with Relationships.)

If you like, I can upload my database so you can actually see it, however an expert like yourself may rage at me, because i'm not an expert and it is for a GCSE Project.

Also I have no idea about "Boolean" or what they are, how to use them etc

Thanks,
Ryan

[iProRyan]

I got this working using the following code:

If IsNull(DLookup("[DVDID]", "Rentals", "DVDsID=" & "DVDID")) Then
Check35 = True
Else
Check35 = False
End If

However to do this I had to rename a field in my table from "DVDs ID" to "DVDsID" and doing this messed up a lot of other DLookup Formulas in my Overdue Rentals form, in this form the dlookup is used to retrieve DVD Title, Certificate etc.

How do I use the space in the Visual Basic I have tried "DVDs ID" and "DVDs_ID" but these still give errors..

Any ideas?

[Robeen]

To keep the space in yoru field name, just put the field name in square brackets like this:
[DVDs ID]
That should solve your problem.
I think a lot of us who have been using Access [or other programming languages] for a while - tend not to put spaces in field names.
Personally, I prefer to use an underscore like this:
DVDs_ID
That resolves the issues that arise with using a space in a field name - and at the same time keeps the field name easy to read.

All the best!!

[iProRyan]

Works great!

Thank you so much for your help and the tip!

I never use spaces in my visual basic 2010 projects, but as this is my first "major" access project I didn't really know, but thanks for the tip!

Ryan

[Robeen]

Happy to help!!
If your problem has bee solved, perhaps you could mark this as solved - using the 'Thread Tools' at the top of the page.

[iProRyan]

Step of head of you Already been done. Although I am a newbie, i'm not that new. :L

[Robeen]

lol . . . All the best!!
P.S. I lived in England for 4 years . . . I still have family in Essex.
I was in Essex, Bedford for a year, Leeds, Edinburgh . . . and then London [Chiswick] for a couple of years.
We see a lot more sun here in Oklahoma!! But summers are scorching . . . you wouldn't like them at all