I have two four tables. Invent, trans, NewOrder and reorder tables. The new order table is not joined by a relationship unlike the others which are joined with the Invent ID field.
The new order table has the fields(ID,QTY,NAME,Order Date). I want to use this to order new items that are not in the Invent table. What do I do to check that I don't enter a new item name in the NAME field that is already present in the Invent table?
Im trying to make a basic customer database and pos system. The part iam having trouble with is, I have two tables as follows:
Inventory: orders(invoices):
productId ProductId
amount (in stock) amount(sold)
pricePerUnit CashTendered
I want the amount(in stock) sold in each order to be deducted from the total in the amount(sold) automatically.
Hi folks,
I have this situation: a table with patients information and IDs, and a table with evaluations for those patients (identified by ID).
I am trying to make it so that the user can't add the same evaluation twice. I want to do it by checking if there is already an evaluation for that patient with the same date.
I know how to show evaluations only for a patient, but I don't know how to check if the value entered by the user in the date field is a duplicate (case in which he shouldn't be allowed to enter it). I assume this is done somehow through the "validation rule" property? But how should the expression be?
Thanks a lot!
I would like to check to see if a previous date value has been entered for a specific record and display a message if it has. I have spent some time researching vlookup and vcount but im not quite there in what i want to achieve.
I have a data entry subform, linked to my main form through StairID. The subform is called tblEstateInspection and has a PK called estateinspectionID, stairID is a FK and links it main table.
Like i say i have been investigating what other people have done using dlookup / dcount, what i want to do is check that a specific date has not been entered for another inspection for the same stairID, i guess what i have got below at the moment just looksUp the value and because it is never null the messagebox always shows? How can i ammend it so that if it finds records that have same stairid, checks Inspectiondate field and only shows message if the new date matches old date
Private Sub InspectionDate_AfterUpdate()
Dim strFilter As String
Dim check As Variant
strFilter = Me!StairID
check = DLookup("[inspectiondate]", "tblEstateInspection", "[stairID] =" & strFilter)
If Not IsNull(check) Then
MsgBox "You have entered this exact date for this staircase before, be careful!", vbExclamation
End If
End Sub
Thanks
Hello everyone I am an access novice in seek of some help,
I'm trying to add a check box to my customer table and then query all the rows that have been checked. How do I go about doing that?
I've put together a very generic database of how I would like to do it. Including the table, form, and query.
I'm just stumped on this issue for some reason.
Thanks for your considerations,
Nick
working through validation rules etc at the minute on my database. Have come across this one is sort of stumping me. I have a tick box in the field "Medication to be repeated" the next field after it is "Date to be repeated on". I am looking a validation rule which makes it that if the checkbox is ticked then date to be repeated must contain a valid date value.
View 8 Replies View RelatedI have been creating a Access database to hire bikes out. I have created all the tables etc but i am stuck creating a query i want to create. I want to have a sub form on my booking form from a query which will show all the available bikes that can be hired. I have looked at the lending library sample and this hasn't really worked, My database is set out differently.
I have attached a version of the database to the post, this is zipped with some screenshots which may be useful.
In my Access app I need to get the latest record added of an item from a MS Sql table and check the period between now and the date saved in the record.
So my guess is:
Select TOP 1 * from tbl_StockItems
Where StockId = Loc_StockId
I then need to check the days between tbl_StockItems.LastStockDate and Today.
How would I write an Access VBA query to give me the amount of days and put that number into a variable.
I have a form that simply lists the items:
DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE:
If IsNumeric(DEPARTMENT_NBR) = False Then
If DEPARTMENT_NBR <> "00" Then
MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly
DEPARTMENT_NBR.SetFocus
GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks)
End If
End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day)
- contains 'name' 'date' and '# of hours' worked
- an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month)
- contains the number of contracts worked that is derived from seperate system
- this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January)
- the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours)
- employees dont have access to this system to enter their own # of contracts keyed on a daily basis.
- for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
how can i make a image appear in my form when there is a check in the check box from the table?
View 14 Replies View RelatedHey all.
I'm trying to get a combo box to populate the box next to it.
Basically pick a project number in the 1st box and have the project name show up in the second box.
I have it working, but the problem is that when it puts in the name of the project, it fills the whole column with that name, and not just the one box.
Here is what it's doing:
http://img156.imageshack.us/img156/2659/dbhm4.jpg
Here is the code im using.
Private Sub ProjectNumber_AfterUpdate()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
ProjectNumber.SetFocus
If ProjectNumber.Value > 0 Then
strSQL = "SELECT * FROM ProjectList WHERE ID = " & ProjectNumber.Value
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
Me.ProjectName = rs("ProjectName")
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If
End Sub
Let me know what I can do.
Thanks!!
Long time lurker, first time poster.
I'm in need a of a check-in/check-out application for my company. We have about 550-600 employees at any given time and our turnover is about 20-30% per year. Our check-in/check-out process requires our employees to personally visit between 30-40 areas in our company (personnel, safety, credentials, parking, insurance, etc) within the first month of employment. This is currently done manually and is a huge drain on labor, especially when check-in sheets are lost, misplaced or, in some cases, forged.
I'm looking to build a database that would be intranet based, password secure (by check-in area) that would allow the new employee to present at a particular check-in, check-out site, complete that portion of the check-in/out process and then allow the person responsible for the check-in/out to enter the status into the database. At any point in the process, I would want to know the status of the person checking in/out (how long they've been checking in/out and what portion of the process have they completed).
Can this be done in ACCESS?
Thanks
Hi
I have sendkey "{f7}" on loss focus.
this works great as a spell check but then I get the mesage box that spell check is complete.
How do I stop this box from occuring?
Is it possible to put a "tag" (probably the wrong term) on individual record entries? So that on a shared database the administrator could see who entered what. That way if there is a problem the administrator would know who to speak to or who needs retraining/firing ha:)
Ive looked around and havent come up with anything.
Thanks
Hello
I'm currently doing a query, when a report is received, we enter a date, i would like a query which would show records where the received date has not been entered. i have tried putting in isnull() but does not work.
Can anyone help?
Thanks
Hi
I have a field that contains some kinds of the student status, such as "Current", "Alumni" and "Expire". The table has already got a lot of records. How can I make a new item available to the field, for example, "Other", without entering a new status?
Like, "Student A" is "Current" but he is now "Other" status. When the user see his record, he/she needs to change the student status from "Current" to "Other". I don't want the user to enter "Other" manually. I want it to be available from a comb box at the first place. How can I add a new item into a field without entering a new student record?
Sorry, I am unsure that I explain enough ..
Thank you.
Gaufres
OK heres the deal:
I have an asset inventory database that i've made where item numbers 1000-1999 is one category 2000-2999 is one category, 3000-3999 is one category etc.
I've made a drop down menu with the choices, 1000, 2000, 3000, etc.
What I want to be able to do is, I want the computer to generate the item number from that drop down menu's selected choice. For example if the category 1000 is chosen i want the computer to be able to generate the next possible item number (without duplicating any item numbers).
Is this possible?
Thanks much for any help :)
Hi everyone i was just wondering if anyone new how to do the following;
Data is entered into a text box named txtItemNo on exit a search is performed and the next textbox txtDescription displays the results.
There is a table with itemnumber and item description in it the results would be the description.
Any ideas? This is on a subform, dont think this amkes any difference though
Thanks in advance :)
My query is executing on everything in the listbox. I only want it to be executed on the value that i select. What codes can i use? I really need some help on this one.
View 1 Replies View RelatedWhen a user enters an item manually into a Combo Box MS Access Help shows how to add it to the 'Value' list. I need the code to add it as a record to the underlying table.
Note: The table has only one column.
Thanks
Hi all,I'm working on a library database for a small collection.I have two fields, 'Date Out' and 'Date IN'. Is it possible that whatever date is written for the date taken out, I can set the 'Date IN' to be 7 days later than that date?Thanks in advance.
View 4 Replies View RelatedGurus,
I seriously need some help with the following.
What I need to do is when I enter the data in the form and before I go to the next record I want to save this row in the table and then insert two more rows with the same values (that I just entered and saved in the table) while incrementing the index field. I also want to programatically manipulate a field value in the second and third rows that I will insert before commiting.
For Example:
Tbl-A
Reg_Num Number (Index)
Name Text (30)
Reg_Type Text (1)
Category Text (10)
Sub_Category Text (10)
Short_Desc Text (10)
Long_Desc Text (50)
1st row:
10001, "PSUS", "A","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"
After I enter these values I click on "SAVE" button. When I click on save, I want to insert the same row twice (notice Reg_Num and Reg_Type values) in the table and increment Reg_Num and change the value of Reg_Type.
2nd row:
40001, "PSUS", "W","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"
3rd row:
60001, "PSUS", "X","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"
As you see, I am incrementing the index field value by 30,000 if Reg_Type is "W" and by 50,000 if the Reg_Type is "X". What it means is copy the row that I just entered, insert it in the table, change the Reg_Type value and increment Reg_Num based on the business rule. COMMIT. Do the same for the next row.
I have about 20 Fields in the table. I do not want the user to type 10 times the same values and make mistakes. This is just an example. I have about 8 different Reg_Types that needs to be stored in the table everytime a new Reg_Num is entered with Type "A". User can enter any Reg_Type but to make my life easier I'm restricting to start from Reg_Type "A". For each Reg_Type the Reg_Num needs to be computed because it is extreamly important in our application.
Hope I explained it right.
Thanks a million in advance.
PS
Hi All,
I've got a form with a series of combo boxes which are dependent on each other, however i keep finding that after ive entered some data and left a record, when i then return to the record, some of the information ive entered has been deleted and the combo box is blank.
I think it must be requerying the combo boxes everytime the record opens but i dont know why, i have only put requerys "OnChange" because that is the only time the dependent combo boxes should be requeried.
Once the info is entered and ive moved on to a new record, i need for it to stay that way when i return to the record and only change again when i edit it.
Help please anyone, its been doing my head in.
Cheers
K
I have a form that the user will enter data, when the user has finished entering the data, i want them to click on a button, with a yes or no option button on it.
if the user selects yes (approved)......i want the record to be emailed to a few peoples email address.
i was thinking that i could store the email address's in a table and email the record to whoever appears in the list.
if the user selects no (Amend Record)..... i want the set focus to go back to the first field of the last record for Amendment.
Can this be done easy enough ?
if so how please.
Cheers
Andy
I have a form with a number of entry fields and after a button is clicked I write to an MS Sql datatable. Everything works fine as long as I put a value in each field. If I do not put a value in one of the fields the sql does not run and the record is not added / updated in the database. I have tried checking the length of the data in the field using
Ent_Length = Len(tb_TagDesc)
or
Ent_Length = Len(tb_TagDesc.Text)
or
Ent_Length = Len(tb_TagDesc.value) but nothing shows.
I am using Access 2007, 32 bit Windows 7