need help with complex data validation

I am developing a form to process training taken by caregivers. On the form I have a field where the user will select the class. After the class is selected I must validate if the caregiver can actually take the class or not. This depends on how many times they have tried taking the class or if they took another version of the class. I am at a lost here of how to work through my flow chart PDF file that I am attaching.

The form is populating a table in my code I tried to create a query for use as a lookup for my validation but when I run the code I get an error message that says it cant find the query I am looking for.

Here is the code I have so far:

Private Sub ComboClassName_BeforeUpdate(Cancel As Integer)
'Declare and instantiate the object variables
Dim dbs As Database, strSQL As String

On Error GoTo Error_Handler

'Set connetion to current database
Set dbs = CurrentDb

'Set strSQL
strSQL = "SELECT Training, CaregiverName, Count(CaregiverName)AS NameCount, ClassName, Description FROM Incoming_Invoices " & _
"GROUP BY Training, CaregiverName, sum(NameCount), ClassName, Description " & _
"HAVING (((Training)=True))"

If Me.ComboClassName.column (0) = 2 And DLookup(ClassName, strSQL) Like "*FOC*" > 1 Then
MsgBox "Caregiver maybe not be eligible for payment. Please check past invoices for other classes taken."
Cancel = True
End If

Exit Sub
MsgBox "An error occurred. The error number is " & Err.number & _
" and the description is " & Err.Description
Exit Sub

End Sub

Post your answer or comment

comments powered by Disqus
This should be easy for a lot of people out there.

I need to write a data validation expression that specifies that entries in a [ReportNumber] field cannot end in a letter. I have a feeling that I need to utilize the Right() function, but I'm not exactly sure how.

Is this enough information or should I explain further? Thanks for any help/hints.

hi guys need help with nested data
thanks before and sorry if my english bad

i have 4 form
form 1 = Date In
form 2 = From
form 3 = Item List
form 4 = Serial Number

this is my relationship pic

this is what i want to make

the problem is:
1. when fill same supplier but different date = can't
2. when fill same item but different date & supplier = can't too

what i want is for example on date 5/8/12 supplier alex sent keyboard and mouse, and 6/8/12 alex sent again keyboard and mouse
how to solve that
thank you

Copy of

I am need help with a sort query that seems to be complex and I can't seem to get the query to sort in the certain order I need. I am not sure how to go about it.

I have 6 fields that I am using as to sort with. We will call them Fields A thru F. Field A=Job, B=Employee, C=IN/OUT status, D=Date, E=time, F=AM/PM status.

I can get the query to sort by A, B & D fields. But I can't get it to pair the IN/OUTs together. For example Jane logs into a job (Sample) @ 8 am on 8/8/10 and works on it until 1 PM. Jim logs into the same job (Sample) @ 10:15 8/8/10 am and works on until 12:30.
I want the query to look like this:
Sample Jane OUT 8/8/10 1:00 pm
Sample Jane IN 8/8/10 8:00 am
Sample Jim OUT 8/8/10 12:30 pm
Sample Jim IN 8/8/10 10:15 am

I will be using this information in a report later on so don't know if I should run it in a query or a report or even put it in a table to use for the report. I just don't want to have to run the query on all the data every time I need it for a report.

Thanks for you help


Need help with Access VBA & Recordsets

Have an access DB and am coding some VBA on the back end. When trying to go to a new record I am having some problems.

My main form fTransaction has a series of buttons which allow the user to open a popup form with some extra data(all data is in the same table).

Things seem to go well, and I can edit the data fine until I try to go to a new record.

The popup form will create a new record but then when it closes I want the main form “fTransaction” to be on the same (newly created) record. Here is the trouble. I cant get the form to go to the same newly created record. I am guessing that the main form is not working with the same recordset that the popup form has????

Since this is my first foray into using recordsets I assume my coding and or logic is not correct. I am willing to send the database to you for review.

Please help

Have attached DB



Step Instructions
1. Start Access, and then download, save, and open the database a03_ps2_Hotel2. Save the database with the name Lastname_Firstname_a03_ps2_Hotel2, replacing Lastname_Firstname with your own name.

2. Create a query that allows you to look up a reservation by entering a customer's full name. Use the tblGuests and tblReservations tables. Combine the GuestFirstName and GuestLastName into a calculated field named FullName from tblGuests. The first and last names are separated by a space. The query results should list FullName, as well as ReservationID, CheckInDate, NightsStay, and NumberOfGuests (in that order). Do not save the query yet.

3. For the current query, the query should display Enter a Guest's First and Last Name with a space in between, such as John Smith when it runs. Sort the query in ascending order by CheckInDate. Make sure that all reservations that are displayed are dated on or after today. Run the query and enter Susan Wenner to test it. Save the query as qryFindReservation. Close the query.

4. Create a query to list all reservations, the guest's room service and restaurant charges, and calculate a mandatory gratuity. Use the tblReservations and tblRoomCharges tables. Your query results should show ReservationID, CheckInDate, ChargeCategory, and ChargeAmount, in that order. The ChargeCategory field should display all charges made at Indigo5, Terra Cotta Brew, Silver Moon Lounge, and Room Service. Do not save the query yet.

5. Add a calculated field to the current query named Gratuity. This field should use the ChargeAmount field and calculate a gratuity of 18%. Format the Gratuity field as Currency. Sort the query in ascending order by CheckInDate. Run the query, save it as qryCharges, and then close the query.

6. Create a new query using the tblReservations table that lists guests with future reservations. The query results should list GuestID, CheckInDate, Crib, Handicapped, and RoomType, in that order. Do not save the query yet.

7. Your query should list the guests that have future reservations for a double room. Look at the data in the tblReservations table carefully. Notice that double rooms have a room type of either Double (1 king bed) or Double (2 queen beds). Ensure your query lists all reservations for both kinds. Sort the query in ascending order by CheckInDate. Run the query, save it as qryDoubleRoom, and then close the query.

8. Create a query that lists the guests who reside in AK, MT, or IA. Use the tblReservations and tblGuests tables. The query should list GuestFirstName, GuestLastName, Address, City, State, ZipCode, CheckInDate, and RoomRate, in that order. This list should also only contain guests who will be paying between $300 and $400 for their room. Sort the query in ascending order by CheckInDate. Do not save the query yet.

9. Create a new field in the current query that calculates the date that is 14 days before the CheckInDate so front desk employees can call the guest and confirm the reservation 2 weeks prior to their arrival. Call the new field RSVPCallDate. Run the query, save it as qryGuests, and then close the query.

10. Create a new query. Use the tblReservations and tblGuests tables. The query results should list GuestFirstName, GuestLastName, ReservationID, CheckInDate, NightsStay, and NumberOfGuests. Do not save the query yet.

11. Add a new field called CheckOutDate that calculates the guests' check-out date based on when they check in and how many nights they are staying. Sort the query in ascending order by CheckOutDate. Run the query, save it as qryCheckOutDate, and then close the query.

12. Close all database objects. Close the database and then exit Access. Submit the database as directed.

For my report I would like to have crystal pull up the previous months sales data. So for example if this month is October 2009, then I would want September 2009 sales.

But I've come across a problem that I need help with, let's say that the current month is January 2010, then I would want Sales data for December of 2009. I don’t know of a formula since I would be pulling data from the previous year.

So if here is my basic formula that I have. (I need help with the first line).

If Month (CurrentDate)=1 then (Need help here);
If Month (CurrentDate)=2 then {IM9_ItemSalesDetailWhse.QtySoldPeriod1};
If Month (CurrentDate)=3 then {IM9_ItemSalesDetailWhse.QtySoldPeriod2};
If Month (CurrentDate)=4 then {IM9_ItemSalesDetailWhse.QtySoldPeriod3};
If Month (CurrentDate)=5 then {IM9_ItemSalesDetailWhse.QtySoldPeriod4};
If Month (CurrentDate)=6 then {IM9_ItemSalesDetailWhse.QtySoldPeriod5};
If Month (CurrentDate)=7 then {IM9_ItemSalesDetailWhse.QtySoldPeriod6};
If Month (CurrentDate)=8 then {IM9_ItemSalesDetailWhse.QtySoldPeriod7};
If Month (CurrentDate)=9 then {IM9_ItemSalesDetailWhse.QtySoldPeriod8};
If Month (CurrentDate)=10 then {IM9_ItemSalesDetailWhse.QtySoldPeriod9};
If Month (CurrentDate)=11 then {IM9_ItemSalesDetailWhse.QtySoldPeriod10};
If Month (CurrentDate)=12 then {IM9_ItemSalesDetailWhse.QtySoldPeriod11}
else 0

Thanks to anyone that can help me, I really appreciate it.

I'm newer to Access...and I'm using Access 97
I need help with one of my queries. It's probably something really simple, but seems to be a little over my head at this moment.

I import collection data from another program that gives me the acct#, transaction date, collection amt, and a running total. I want to make a query that will show me the largest running total between two dates for an acct#. Any help would be greatly appreciated. Thanks!

I need help with a query and the control source of a combo box...I guess there might be several answers here, but here we go...

I am building a new (my first) database to keep track of certain data received monthly from several different countries. I was going to save all the data in tblReviews which has an individual ReviewID for each review (each monthly review from a particular country).

I want to give the user the option of looking at, for example, sales for December 2011 for 10 different countries. This is where I run into trouble...I want to have a combo box for the month and another one for the year. How would I set this up to then be able to utilize the user's selection in a query? Would it be easier to save the review dates in two individual fields?

Also, can a query take information from check boxes (where the user would pick which countries he or she wants to compare?) Thanks!

I need help with a problem which I cannot seem to solve, I have a form which contains a list of companies and once you select a company from the drop downlist it opens up a subform containing service calls made to that compnay which contains fields such as docket # and service dates and the docket number is a hyperlink to the actual service call form. Now you can have duplicate docket numbers and service dates. I need to be able to open the form based on the docket number and services date. I problem is that some service calls get opened with data within them and others do to not. Any suggestion on how this can be done?

I have a form that contains text boxes for inputting a date and one for inputting a time. Ive got the date box doing great with calendar control. What I need help with is setting up an input mask for the time box in such a way that the user doesn't have to worry about putting in colons to separate the hours, minutes and seconds. I want the user to just be able to enter, for example, "6', hit the tab key, enter "10" , hit the tab key, enter "P" or "A" ("610P") and then go on their merry way. Does anyone have an example of this, or a suggestion?

Related to my brain-dead plight, what might be a good way to validate a correct/incorrect time entry? Our tables do not use military time.

You folks are great! Can't wait until I feel comfy enough to start feeding back some suggestions to this forum and in turn help others!



Ive got an unbound form which i have linked to my database.
What im trying to do is when the form is open, it pre-fills with the last record that was entered into the databse.

Now ive got it looking at the database and it does bring up the 1st record.

What i cant figure out is how do i get it to look at the last record in the database.

This is my code so far:

Dim getTableRecord As DAO.Recordset
Dim dbs As Database

Private Sub form_load()

Set dbs = CurrentDb
Set getTableRecord = dbs.OpenRecordset("tblProduct")

txtProdID.Locked = True
txtDescription.Locked = True
txtCategory.Locked = True
txtSize.Locked = True
txtQuantity.Locked = True
txtPrice.Locked = True

txtProdID.Value = getTableRecord("ProductID")
txtDescription.Value = getTableRecord("Description")
txtCategory.Value = getTableRecord("Category")
txtSize.Value = getTableRecord("Size")
txtQuantity.Value = getTableRecord("Quantity")
txtPrice.Value = getTableRecord("Price")

End Sub

what that does atm is prefill the boxes but with the data thats on the 26th record. Got no idea why thats happening. So how do i get it to load the data thats on the last record??

Thanks for the help..

I have attached screen shots from the database that I need help with.

What I am trying to accomplish is in the Form "Assets" that was created from the Table "Assets". I want to populate the Validation Protocol field (below the blue line) and have all of the other information above the line (8 fields) populate automatically. The information that needs to go in these 8 fields is located in the "Asset List" table. How can I add this information automatically based on the entry of the Validation Protocol number?

The reason I need this is because when I add more protocols in the future with the same number, I dont want to have to type all of the information in each time. What will differentiate each record in the form will be the "Vol" field (volume). Also, in the future, I will be adding new assets into the "Asset List" table with each asset having its own protocol number (ie, the next asset will be VP3502, and so on).

I have been searching online for an answer for more than a week, but I cant seem to find out how. Is DLookUp the best way to do this or another way?

Someone, PLEASE HELP!!! I am not VB savy, so I am trying to use similar functions that are used in Excel.


I am aboard a US Navy ship and have created a data base to keep track of my users. My users have to do a yearly training about keeping the network safe and if its not done they loose their access. The table I have keeps track of the date of their current training and when it expires. What i want is in a report that i generate, which has all the users listed with the date of their IA expiration to have the users who are a month from expiration to be in yellow or some other color and the users who are past expiration to be red. Can this be done? How would I do it? Please I need help with this...

Thanks in advance.

"Never Forget"

Recently I started a thread needing help with formatting a report.

Here is the thread

I got a response, and tried out the attached db. But I can't seem to get it to work. I get an error when I try and open the report via the form, so It tried running the report itself, and found that anytime that there is a month that doesn't have an entry for each day, I get an error.

Does anyone know how to make this work?

Or does anyone have an alternate way to create a report in this format.


What I basically need help with, is how do I combine my module into access, how do I use it, say for a example i've built something I couldn't build with a query, and I want to.... say make a ComboBox based on what I've built, how do I do it??
and another thing, say I've filtered out some lines from a table using VB, how do I use it like a query?

thx in advance, me

To anyone who can help: I have designed my tables with care, and am ready to make forms for users in my companies database, but here is what I need help with, I need to be able to enter in each field, but when I get to a field which draws from a another table, say Customer, and there is no such Customer, to automatically go to the Customer Form, and force the user to add that Customer, and then bounce back to the order form.
I hope I made sense, and I know its a tall order, I appreciate any help..

Hi everyone,

I need help with this template is for a small company that I work for and the template is basically what I need to create an Inventory but I have 2 problems, in the Transaction List I want to be able to have the same product from different locations, but when I changed one location it changes all of them, and I also one to add a column call destination with different buildings, for the same product. I'm just a little familiar with microsoft access

Hi Realy Need help with this query. I have a query called query 1 that calcualtes the rank of each player by points, however instead of going 122356 etc i want it to go 112345 or if someone has the same points shows = as in =1st place. the tie break if two players would be plus.minus field like you would have in a football league table. i dealy want it to rank 1123345 etc Im no good at coding so if anyone know who to do this please adjust query 1 as you see fit you help would be much apreciated/
Attached Files
Test1 (2).zip (400.9 KB, 0 views)

I need help with how to create what I need the database to do. For work, we need to make comments on how each person performs throughout the year. I need it so that each person has their own page and the supervisor has the ability to add comments with a new box for each comment. How do I do this?


I am planning a key database

I have planed to use access and visual basic.

VB to add and delete keys.

Access to do the rest.

Is this a good solution? Do you have a better one, pleas let me know.

I need help setting up the delete and add buttons, I am bad i vb.

And I need help with the Delivered button, when I push the button I want it to delete all the information on the page. But print a backup or save it as a file, do you have a good idea.

When I press the “add key button”, I want I to add a key.

If you have 3 keys, and press ADD key, then it adds a 4 key and it must be a scroll window. Then I can choose the key I gave him

There is a image of the plan I am having.

Hi all, haven't been on in a while but really need help with a part of my DB, i have a table on which orders are placed, once the user prints the order form the table is cleared. However what i would like to do is duplicate this order into another table with an Order Ref Number,and then using a form, once the stock is received allow the user to update what is in stock by placing a tick box next to the items this will then update the record with what was ordered and then remove that item from the 'OnOrder' Table leaving only what's left to arrive on the table.
I understand that this could be complicated but really need help on this as my DB is nearly finished and this is the only part left to conquer.

Cheers in advance

Hi i need help with a scenario,

I have made the database and the tables but i am having trouble with the forms and bits of the realtionhip.

here is the scenario

Current System
The case study is based on the video issue recording system of the Video Library (VL). VL specialises in obscure videos that are not readily available from other sources. VL lends only to members of the Video Society, part of the exclusive and very expensive Gentleman's Club. Details of finance are outside the scope of the investigation. Any member of the club is automatically a member of the library. Club membership is for one year. A few new members are admitted on 1st January each year; new members are not admitted during the year.

The library is set up along conventional lines and members are not restricted in the number of videos they may borrow at any one time. Videos are requested for return only if required by another member. Most active users of the library regularly change their videos. Two or three copies of popular titles are available. Two codes are used to describe a video. While a video publication can be identified by a standard 7-character alphanumeric video code assigned by the producer of the video, a particular copy of it has a 6-digit serial number, the copy number, given by the library itself.

In order to borrow a video a member selects it, takes it to the issue desk and gives the video and his club membership card to the librarian. The librarian takes the video ID card from the video and writes the membership number on the card at the end of the list of membership numbers of previous borrowers. The librarian places the card into the loans file in video code order and the member leaves with his video.

When a member returns a video, he presents the video to the librarian. The librarian finds the video card in the loans file, lightly crosses out the member's number and places the card in the video sleeve before returning the video to the library shelves. The crossed-out numbers provide a simple history of the loans of each copy.

To reserve a video which is on loan, a member asks the librarian and leaves his membership number. The librarian finds the record of the relevant video in the loans file and adds the member's number to the reservation column on the video card. The librarian also makes a note of the member who has the video on loan and completes a return request card addressed to that member. The address is taken from the library copy of the club membership book. When the reserved video is returned, the librarian puts the video under the counter and completes a reservation ready card addressed to the member who first requested the reservation, again taking the address from the membership book. The date by which the reserved item must be collected is entered on the card. The member requiring the video can then pick it up from the library and have it issued in the usual manner. Videos which are not collected by the collection date are returned to the shelves or offered to the next reserving member, if any.

The selection, ordering and payment for the videos is outside the scope of the investigation.

On receipt of a new video the librarian has to make out a video card for insertion in the video sleeve. The librarians are also responsible for amending the library's membership book with changes of address, and at the end of the year for collecting the new membership book from the Club Secretary.

Proposed System
The functionality of the new system is to be the same as in the current system. That is, operations which can be performed in the current system should be capable of being performed in the proposed system. For example, the history of loans of a copy of a video is required. Paper-based documents will be retained except that video cards are abolished.

Is someone can help me with a simple solution, many thanks


I am learning Access and need help with a query for a list box using table below. I am trying to create a query that generate only RequestDate and TransactionType that do not repeat. The targeted result would look like:

2/5/2012 new
3/3/2012 modification
4/1/2012 renewal

Is that possible?

Thank you in advance
RequestID LoanInfoID RequestDate TransactionType DocumentDate LoanDocListID Description
12 1 2/5/2012 new 2/6/2012 1 ...
13 1 2/5/2012 new 2/6/2012 2
14 1 2/5/2012 new 2/6/2012 6
15 1 3/3/2012 modification 2/12/2012 8
17 1 3/3/2012 modification 2/12/2012 7
18 1 3/3/2012 modification 2/12/2012 11
22 1 4/1/2012 renewal 3/3/2012 4
23 1 4/1/2012 renewal 3/3/2012 6
25 1 4/1/2012 renewal 3/3/2012 4

Hello I need help with a query I am trying to create. I have some knowledge of Access but I am no way close to being an expert. Ok I will try to explain:

I have a table that I need to search for values, howevere some of my people have mutliple values. An example has been attached as a pdf file, I had to do this because I could not get the example to display correctly in the text area provided for this forum.

Well thats the problem, please help if you can.



Not finding an answer? Try a Google search.