Is there any way user can verify the data that its enter correctly, for example I want a button on user form, after every 14 entries when user hit that button it bring the sum of 14 entries.
Hi I have designed a database for a small organization. It is a ver small database. can any one verify and tell me the mistakes in my design. It hardly takes 20 min for the people like you. This is my first step in designing. I am attaching the zip file which contains dbm file and business requirements. Complete physical data model is there.
especially in "category_tests" table i feel that i have done some thing wrong.
This is my first question posted on this site. I have created a survey with questions to be answered. The database is already populated with records of the people's names who were getting the survey. What I need is when the data entry clerk brings up the customer's name on the form, is that they are forced to enter the surveys answers for certain questions before they click the "Save" button. The answers are set up as radio buttons on the form (ie Less than 6 Months, 6 Months, 8 months and 1 Year).
Not sure if a Before or AfterUpdate macro is needed?
Any suggestions would be appreciated. Please email me directly if you have code.
Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.
Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:
For APR:
4/1/14 Dep A 12 2 4/1/14 Dep B 10 2
However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?
I am currently looking for a way to add data in a form with picture and then be able to view all entries with data.
Basically what I want to see is "product image > item number > client > description > material" with for example 500 entries, i want to be able to see a whole list of all entries. I would like to be able to make every entry as described above.
I'm using the code below to check new product# input from a form called frmProducts that is bound to a table called tblProducts.
Private Sub Prod__BeforeUpdate(Cancel As Integer) Dim varX As Variant varX = (DLookup([Prod#], "tblProducts", Forms!frmProducts.[Prod#])) If Not IsNull(varX) Then MsgBox [Prod#].Value & " already exists as a product #" Me.Undo Cancel = True Else 'do nothing! End If End Sub
The problem is that any new product# that you input results in "product already exists".
Hello, I need some help. I have a form named MASTER based on a table also called MASTER. A control of my form in names SSN which stores the client SSN.
On the same form I have placed a subform which has its record source to a table named 21.
What I am trying to do is: When I enter a new record in my form MASTER, the code should: 1) Verify if in table 21 a record with that SSN already exists. If YES, skip the code and do not add the SSN. If NO, well, run the appendquery.
2) At the same time, verify if a record with that SSN already exists in table MASTER. If NO, add the data, if YES, abort the code and exit the form.
The form EForms is a menu form I use to access the records.
This is the code. Something is not going right, I think with the end if functions. Any help? I have placed the code in the AfterUpdate event of my control SSN.
Dim mydb As DAO.Database, MyRs As DAO.Recordset Dim strCode As String Dim strFilter As String Dim stDocName As String Dim stLinkCriteria As String
Set mydb = CurrentDb Set MyRs = mydb.OpenRecordset("master")
stDocName = "MASTER"
stLinkCriteria = "[SSN]=" & "'" & Me![SSN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
If Not IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then Forms("EFORMS").Visible = False Else If IsNull(DLookup("[SSN]", "21", "[SSN] = '" & Me!SSN & "'")) Then DoCmd.OpenQuery ("Appendssa21tax") End If End If
If Not IsNull(DLookup("[SSN]", "MASTER", "[SSN] = '" & Me!SSN & "'")) Then MsgBox "Sorry! A record with this SSN is already in file. Retrive case from E-Forms Menu.", vbOKOnly, "Warning" DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 DoCmd.Close End If Forms!eforms.lstPreInterview.Value = Null DoCmd.Close DoCmd.OpenForm ("Eforms") DoCmd.RunMacro ("CloseEforms") DoCmd.OpenForm ("Eforms") End If
I have a group of databases, with one that I keep global values in (among other things).On initial load, I link the table to other databases, but the linked tables pile up.How can I verify the linked table exists, so I don't have to link it again?
I would like to have a button on my form that would update a record to a closed status, but I want to have an "Are You Sure?" window pop up first. I did a search on "verify," "verification," "sure," and several others but haven't found what I need. I looked through my Access bible, with no luck.
i have a report that displays 12 fields of date sorted by a case number. I have a problem when there are two or more entries in the db with the same case number, but different data in the other 11 fields. My report displays each of entries in the db for the case number, but carrys the the data from the first of those entries through out all the entries. I'm not looking for an exact solution here, just for someone to get me pointed in the right direction. Is it a sorting and grouping error or something of like?
I have a tblhealthrecord that has many multiple entries for the same animalID. Would like to pull up all records from this tbl for one animal. I can sort them later but just need to know what to ask for so I can read upon them. I at first thought this could be based on date but not really since you could have multiple entries in one day.
I have a query result-set with several columns: A, B, C ,D. All Four of these columns display numbers. From this query, which I will call the "SetUp"query, I want to query it and obtain the average for each of the columns A, B , C ,D. However, some of the values in columns A, B , C ,D are zero. If I use the standard 'Avg' function in Access, it averages the column AND INCLUDES THE ZEROS IN THE AVERAGE. How can I get an average of just all the non-zero entries?
Hi, I'm not sure if this is possible, but I've heard you guys really know your stuff.
I have a client who would like to import a list of e-mail addresses, but would like to verify the domain name used in the provided address against a list of domain names pre-populated in a table. Do I need to use Dlookup? Here are some additional details. Thanks in advance!
I have a picture stored on a network file share that populates each time a particular form is opened. Every so often we lose our connection (which is a different issue altogether). Right now the error I get after much grinding away, is "Bad file name or number"
Is there a quicker way to check for the connection before it spends 45 seconds trying to find the whole path?
Code: Dim vFolderPath As String, dirFile As String, strFile As String vFolderPath = Nz(DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'")) dirFile = vFolderPath & Dir(vFolderPath & ctrl_people_id & " *", vbDirectory) strFile = dirFile & "profile_pic.*" 'Debug.Print dirFile On Error Resume Next If Dir(strFile) <> vbNullString Then Me.[ctrl_ImageFrame].Picture = dirFile & "" & Dir(strFile) Else Me!ctrl_ImageFrame.Picture = "X:~stuffprofile_icon.png" End If
As you can see a table can be linked to any number of tables.
Now for my actual question. I don't want to have to type in all the queries and tables. I would like to choose any query and table from a drop down of all possible queries and tables that are currently in the database - like a list, where the list contains all the possible values.
I have a query that feeds a report. This report shows a "Set" and the "Charge Numbers" associated with it. Each set could have a possibility of more than one set of IT numbers. This means there could be say 2 entries for set "D7" and IT numbers for both of them.
As a result when I run the query, and say the set has 2 Charge numbers, it will bring back 2 results, with 2 charge numbers on each of them, which is correct. So if a set had 4 charge numbers assigned to it, 4 results would show with 4 charge numbers for each result.
Is there any way I can make it only show one of those results as they are identical?
I'm trying to use access to identify how many times an indiviual appears in my database. I've got a large file which gives me all referrals, each referral comes with two identifiers, one unique to the person, another unique to the referral. This means that the person unique number may be repeated multiple times (if a person has more than one referral) but the referral number would be different each time.
I've run a query to identify the duplicates, i.e. the records where the person unique identifier is repeated. What I am trying to do is work out now how many people have been referred twice, how many three times etc. The original query looks something like this (but on a much bigger scale).
Person Referral 1 A 1 B 1 C 2 D 2 E 3 F 3 G 3 H
What I'm after is a query that will tell me that two people were referred three times (1 and 3) and that one was referred twice (2) and so on.
I have a form that feeds information to a query which in turn sets up a report. For clarity i will list out in basic terms what I have
Input Form - Check Box to activate/De-activate a text field [chk-active] - Text field for a parameter [txt-Parameter]
the query has a column that has null values from the originating table. These values will be added a t a later date, but need to be queried and reported at some business intervals.
In the query criteria for this column, I have the following Criteria
Like IIF([Forms]![ReportGenerator]![chk-Active]=0, "*", [Forms]![ReportGenerator]![txt-Parameter])[/I][/I]
I have tried for the last 5 hours to figure out how to write the formula to be able to get the blank entries to show up as well but have had no luck.
I have a large table with millions of entries. I want to run a query to return all entries that are on a Saturday. The date stored in the table though is just typical date format eg 15/11/2015.
I have a table called Stock Levels which contains 3 fields. (ID, ProductID, StockLevel) ID is the Pkey, ProductID contains duplicates and StockLevel which contains different stock levels
and I am trying to remove the duplicates and retain the the data so I am left with the correct stock number
what I have done is the following, but I am still getting duplicate values in productid and stocklevels
SELECT DISTINCTROW id, productid, stocklevel into mynewtable from stocklevels
im in the middle of developing a database to log DOWNTIME of various machines in the factory. Basically myself and others will use it to log breakdown details and the amount of time (in HH:MM) the machine was off.
I have a form which will list details of a certain machine in a list box which i can populate using a query.
The list box will display 3 columns: Date of Breakdown, Machine Name and Downtime
I then have a text box below this that i would like to display the TOTAL amount of downtime for that machine (The listbox may have different information in it depending on the query criteria, e.g. It might display a certain month for a certain machine).
THE ISSUE i am having is its proving to be difficult to get the correct sum of the accumulative time to display correctly in the text box.
Is there a way i can use a query to calculate the total time of a column and return a value which is formatted as time (like this hhhh:mm or dd:hh:mm) or preferably use some VBA code on the form itself to total up the DOWNTIME column of the list box and display it with the correct format in the text
I have gotten close, but as the total amount of time exceeded 24 hours, it went past 00:00. I'm assuming it treated it as a clock and went into the next day? This is NOT what i am after, i need the total amount of days, hours spent on the machine and not a time of the day.