now when I try the section performing data entry with SQL, I am at the point where I have typed the SQL statement:
INSERT INTO Employees(EmployeeNumber, [Last Name], Gender, HourlySalary)
VALUES ('227947','Jameson','M',18.85);
It chokes on the "unknown variable" HourlySalary, I could find nothing wrong with it.I then deleted my HourlySalary and copied theirs directly from the tutorial. It still chokes on HourlySalary.Now I am not sure what to do since I have taken their script and copied it to the SQL window.
I have a form frmMember, it has a search button that performs a complex search of fields. after performing my search my [BaseDues] field shows #Name? BaseDues is set by a CBO in the field above.
It works correctly except after the search...
Here is the Code for the Search Button:
Private Sub cmdSearchForm_Click() On Error GoTo Err_cmdSearchForm_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmSearch"
I've join a small company who uses access to store a route card system. Unforunatly it's partially broken and nobody seems to know how to fix it. Instead of bodging it forever i'm hoping to fix it.
My access knowledge is fairly limited so please keep it simple
The route cards are fairly standard, the primary key beening the route card number, the are various fields material, order date, etc.
The problem is when you enter the data into 3 of these fields access repeats the data to all the forms. At the moment we're having to leave them blank and fill them in by hand, which means we don't have the data on computer.
I have this table with this information...TABLE1code.............Name............Amount.....type'1000000'........'Name1'........... 0.00.....'N''1010000'........'Name2'........... 0.00.....'N''1010100'........'Name3'........... 10.00......'S''1010200'........'Name4'........... 5.00......'S''1010300'........'Name5'........... 0.00......'N''1010301'........'Name6'........... 1.00......'S''2000000'........'Name7'........... 0.00......'N''2010000'........'Name8'........... 0.00......'N''2010100'........'Name9'.......... 6.00......'S''2020000'........'Name10'......... 0.00......'N''2020100'........'Name11'.......... 3.00......'S'A need to create consult whose result show like this:RESULT'1000000'.....16.00'1010000'.....15.00'1010300'..... 1.00'2000000'......9.00'2020000'......3.00Could somebody help me this problem?. Thanks
I have a table customers and Purchases. Customer table has the {name, lastname, tele, address, city ..etc.} and the Purchases table has the {name, sku, unitprice, qty, cost}
Is there a way to create a report that shows the total amount of purchases made by customer?
Also is there a way to round up values in reports such that if the value is 3.95 it says 4.00?
This is going to be a simple one for someone, but it is proving to be a pain for me. I have two collums in a table, one that is a value another is a quantity, i have another field that totals the two fields. This works fine and shows the total but it does not store the total into the database, how can i get it to do this.
In the Total field the control source is "=[Cost]*[Quantity]" i have tried to set it too TotalCost=[Cost]*[Quantity] but this gives me errors
I recently got a new job and am trying to learn access for it. I have two tables. Both of them have client id numbers. They are both supposed to have the same client id numbers. However, Table 1 has more client id numbers than Table 2. I want to do a match query that selects the client id numbers in Table 1 that do not have a corresponding match in Table 2. How would I go about doing this?
Also, I want to do a simple select query where I select the client id numbers in Table 1 whose first two numbers are "88." How would I do this in the query or SQL form.
Do you have any recommendations about the best way to learn Access for practical applications like this? I'll also need to get good at making Forms which seem fairly complicated.
I got this to work easily on another form but on this particular form it doesnt work. I enter a command button and name it d1, then I enter this code for it to populate the current date in the field next to the box:
Private Sub d1_Click() Date_Entered.Value = Date End Sub
Can anyone see why it doesnt work :eek:
ERROR IS: Run-time error '2465':
Database cant find the field 'Date' referred to in your expression.
When I enter a date field it enders whats in that field (blank) into the field I told it (Date_Entered) on my form.
I have a form which is based on student records. What I'm trying to get Access to do is to bring up an error message as soon a duplicate student number has been entered. At present I've managed to replace the Access standard error message 3022 with my own by using the following code:
Private Sub Form_Error(DataErr As Integer, Response As Integer) Const conDuplicateKey = 3022 Dim strMsg As String
If DataErr = conDuplicateKey Then Response = acDataErrContinue strMsg = "Duplicated record. Please recheck your data." MsgBox strMsg End If End Sub
However the error message only appears when you try to leave the form (e.g. start a new record). I would like the error message to appear as soon as the user leaves the Student Number text box on the form. Is there anyway to do this?
I am writing an application for my personal use. I have 2 Access databases, one contains the data tables and the other has the forms, queries, reports, etc.
So far, I have been able to use the queries in the Row Source property of my form objects to accomplish what I want to do. Now I want to have a button that appends records to a table, the data in the table comes from several different controls on my form. After doing this the button should requery another control to display the new records added.
The OnClick event for the button allows me to use a Macro builder, Expression builder or Code builder. If I choose to use DAO in VBA code to append the records, do I have to define the database to create a database object to use even though the database is already open? Can I use a Macro to do this? Can a macro reference the data that is in the controls on my form?
I am trying to perform some keyword searches of a text field. My preferred option would be to hold all the keywords in a separate table and then use something like
SELECT * FROM Text Table, Keywords Table WHERE (((InStr[Text Table].[Text],[Keywords Table].[keywords])) > 0 ));
This works and returns anything which has the string from the keyword table, however I wanted to look for whole words only. I can do this in SQL using a regular expression along the lines of [!a-z] keyword [!a-z] which only finds the string where it has no letters directly either side of it.
What I would like to do is combine the two methods so I hold my keywords in a table and then use them with the reg ex to find whole words only.
how to do anything with it outside of tables and queries. I have been tasked with creating a form to do fairly basic things. Via tutorials I have managed to use the Expression Builder to do virtually everything I need except one thing.
I need to pull a value from row 2 in a specific column from a subform and subtract it from row 1 in the same column and subform leaving behind a + or - number for the user. Similarly, I need to do the same but subtracting the LAST ROW from row 1. If there is only one row at the time the operation is being performed, a value of No Data needs to be returned instead of a number.
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
So now, I'm using the code below to prevent duplicate name entry and it is working great - EXCEPT when I enter a first or last name which contains a ' (ie, O'Tool, O'Malley, O'Hern)...anyone got any ideas for me on how to make this not happen?
The error I get is:
Run Time error '3075'
Syntax error (missing operator) in query expression '[Last Name]='O'Hern' And [First Name]='Lori'.
The code i'm using is:
Private Sub Last_Name_AfterUpdate()
'Check for duplicate first and last name using DCount
If DCount("*", "[Constituents]", "[Last Name]= '" & Me![Last Name] & "' And [First Name] = '" & Me![First Name] & "'") > 0 Then Beep MsgBox "This first and last name already exists in the database. Please check that you are not entering a duplicate constituent before continuing.", vbOKOnly, "Duplicate Value" Cancel = True End If
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded 121/2/201310:00:00 AM3:00:00 PM 221/3/20132:00:00 AM11:00:00 AM 321/4/201312:15:00 AM11:30:00 AM 421/5/201310:25:00 PM11:00:00 AM 531/2/201311:00:00 AM3:30:00 PM 631/3/201312:00:00 PM10:00:00 PM 731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
My db has multiple linked tables. As time passes and work on a project continues data needs to be added to a separate table that is linked to the first table to be populated.
i.e., Initial design data is entered first, when drilling is complete more data is added to a linked table, when drilling is surveyed yet more data is added to the second table.
What I want to be able to do is to query for a particular set of existing design data, display this on a form and on the same line enable data entry to other fields from other tables which are linked to the existing design data.
My boss wants me to program in visual basic, instead of using queries, a calculation between times, which values are gotten from a table. How do I perform a calculation between two fields from a table in code and what functions are there to calculate the amount of difference in minutes between times in visual basic? THANK YOU SO MUCH IN ADVANCE!
I was using the following code on a field (ItemCode) that was indexed to prevent duplication of records. The intent is that the user will get an error message that a duplicate exists before they enter all the data for the record and get the built in error message that Access 2003 provides when an index violation has occurred.
Code: Private Sub ItemCode_BeforeUpdate(Cancel As Integer) Dim Answer As Variant Answer = DLookup("[ItemCode]", "tblQuestions", "[ItemCode] = '" & Me.ItemCode & "'") If Not IsNull(Answer) Then MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate" Cancel = True Me.ItemCode.Undo Else: End If End Sub
Now, the index for this is based on two fields (ItemCode and Question Group). I would like to display the same message before update but don't know how to include the second field in the syntax.
I have a shared db where there is a requirement for more than 1 user to enter data in the same form and hence the same table. Up till now there has only been 1 data entry clerk so there has not been a problem.
Would accessing the same form by different users cause a problem, assumming they are inputting different records?
This has most definitely been asked before but I really cant find anything on the forum! Ive created a data entry form which has a save button which the user must click each time to save the record. However how do I refresh the form i.e. empty all data from the previous record without exiting the form. Ive tried to combine the undo macro but that simply deletes the previous record. Any help please!!!
In my database I am trying to produce a "Statistics" function. As part of this, the user will enter a "Start Date" and "End Date" in a form and then click a button which will open the requested report with the date drawn from a query. The code on clicking the "All Jobs" button is:
I am trying to stop the user from leaving the date fields blank or entering dates outside of the range of the database so I have tried the code:
Code: Private Sub AllSalesEnquiries_Click() If Me.DateFrom = "" Then Beep If MsgBox("You have not entered a start date", vbCritical, "Start Date Not Entered") Then
[Code] ....
If the user enters dates within the range of the database the report is presented correctly.
If the user does not enter a date or enters one outside of the range it produces the correct message box however if the user then corrects the mistake I receive a Runtime Error 3071 message. Clicking "Debug" highlights the final line of code:
I know that the code is correct because it works fine as stand alone code and it works if the user enters the correct dates so I am not sure where I am going wrong.
The date format works perfectly for the way dates are formatted in the database.
I am looking at the public function routine, that validates the password entry. I want to know how i can make a message pop up with the specific error the user has made on entry.
Public Function ValidatePwd(varPassword As Variant) As Boolean Dim blnValid As Boolean Dim blnValidCriteria As Boolean Dim intChar As Integer
