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

Sponsored Links:

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?