Relating record in form with a record in query?

[theorythree]

Hello guys and gals, i'm new and I have a most likely dumb question. I'm very new to access and taking a class on it that requires us to create a video store that will rent out movies and video games and i'm not quite sure how to explain this. I'm having a problem with trying to get access to not allow a person to rent out a videogame or movie to a customer if they have overdue movies or videogames.



For example my Video game rental table contains this:

VGRental_ID(PK, autonumbered..This is so that each rental is unique, etc)
VGRent_Out(This is a date field and when I actually rent the videogame out to a customer)
VGRent_DateToBeReturned(This is also a date field and it's for when the videogame is suppose to be returned to the store)
VGRent_ReturnedDate(Date field in which the customer actually returns the videogame)
VG_ID (FK to a VideoGame table which has information on videogames that I have in inventory)
Person_ID (FK to a Person table in which has information on each person that frequents my videostore)

I also have a Overdue Videogames Query which tells me which videogames are overdue and who the persons are that have the videogame out.

Person_ID, 
VG_ID,
 VGRental_ID, 
VGRent_Out, 
VGR_DateToBeReturned,
 VGR_Returned Date 
and also a calculated field called Overdue Days which is 
Overdue Days: [VGRent_ReturnedDate]-[VGRent_DateToBeReturned] 
This tells me how many days that the videogame is overdue.

Now, this is where the brainfart occurs... I don't know how to tie(sorry, I know it's crappy terminology) together the Per_ID in the rental table to the Per_ID in the Overdue Videogames Query to see if a person has Overdue Days. I tried creating a macro for the add record button that went something like this

[VIDEOGAMERENTAL]![Per_ID]=[Overdue Video Game Rentals]![Per_ID] & [Overdue Video Game Rentals]![Overdue Days]>0

So if the person that is trying to check out videogames on the VIDEOGAMERENTAL form is the same person with any overdue days on the Overdue Video Game rentals query that a messagebox would appear, but that formula does not work on the macro.

I'm really sorry about how I worded this because it's really hard to explain what i'm trying to do and it may even be impossible. Any help would be appreciated.

[jzwp11]

Welcome to the forum!

How is the person's info entered in the videogramrental form? Are you using a combo box to select the person? If so, you can filter the combo box to only show people who do not have any outstanding rentals using you overdue query.

Alternatively, you could use the after update event of the combo box to check the person against your overdue query. This assumes that the bound column of the combo box is the person's key (ID) value.

If dcount("*", "[Overdue Videogames Query]","Person_ID=" & me.comboboxname )>0 then
msgbox "The person has overdue items"
end if

[theorythree]

Welcome to the forum!

How is the person's info entered in the videogramrental form? Are you using a combo box to select the person? If so, you can filter the combo box to only show people who do not have any outstanding rentals using you overdue query.

Alternatively, you could use the after update event of the combo box to check the person against your overdue query. This assumes that the bound column of the combo box is the person's key (ID) value.

If dcount("*", "[Overdue Videogames Query]","Person_ID=" & me.comboboxname )>0 then
msgbox "The person has overdue items"
end if

Yes the person's info is entered via combo box. Can you explain how I would do the after update event of the combo box? How would I go about adding that? Sorry, like I said I am very new to access but you have been very helpful! Thanks in advance.

[jzwp11]

From design view of the form, right click on the combo box that holds the people then click on Properites from the menu. Then look for the Event tab and then After Update. Click in the line next to After Update and a button on the far right with 3 elipses (...) will show up. Click that button. This takes you to the Visual Basic for Application (VBA) code window where you can enter the following:

IF DCount("*", "[Overdue Videogames Query]","Person_ID=" & me.comboboxname )>0 then
msgbox "The person has overdue items"
END IF

In the above simple code, I assume that your query is called Overdue Videogames Query and that you have a field in that query called Person_ID. You will need to substitute your actual combo box name in place of the comboboxname in the above. You will need to keep the me. The me. is just a shorthand to refer to the current form where the combo box is located. You can find out the name of your combo box by going back to the property sheet and the click on the All tab then look for the name property (which should be first in the list of properties).

The combo box must have its bound field set to whichever field has is equivalent to Person_ID.

[theorythree]

From design view of the form, right click on the combo box that holds the people then click on Properites from the menu. Then look for the Event tab and then After Update. Click in the line next to After Update and a button on the far right with 3 elipses (...) will show up. Click that button. This takes you to the Visual Basic for Application (VBA) code window where you can enter the following:

IF DCount("*", "[Overdue Videogames Query]","Person_ID=" & me.comboboxname )>0 then
msgbox "The person has overdue items"
END IF

In the above simple code, I assume that your query is called Overdue Videogames Query and that you have a field in that query called Person_ID. You will need to substitute your actual combo box name in place of the comboboxname in the above. You will need to keep the me. The me. is just a shorthand to refer to the current form where the combo box is located. You can find out the name of your combo box by going back to the property sheet and the click on the All tab then look for the name property (which should be first in the list of properties).

The combo box must have its bound field set to whichever field has is equivalent to Person_ID.

Thank you! You have been such a help!

[jzwp11]

You're welcome.