Noob - Update Record In Table
Sep 13, 2005
Hi all,
First of all....my first access database and I'm still a VB newbie...so my apologies if this is a stupid question. I tried searching the web and this forum but could'nt find what I was looking for.
Basically, I am developing an authentication script for a training tutorial. The user either logs in with there existing info, or prior creates a new record. When they login they also select a team (eg Team 1, 2, 3, 4, admin and so on). This is selected from a combo box - the teams are on their own table.
I have another table storing the user info - name, password, the users team...and so on. When they log in and the team selected does not match the team in 'tblusers' - I want access to change the record to reflect the new team.
Can anyone help me with the code to make this happen? So far I have -
If Me.defineteam.Value <> DLookup("Team", "tblusers") Then
MsgBox "You have changed team. This will now be updated", vbOKOnly, "Required Data"
GoTo verifypassword:
End If
This authenticates the team....I just don't know how to replace the one in the record with the value in the combobox.
Thanks for your help.....I'm losing hair!
cheers
Sd
View Replies
ADVERTISEMENT
Dec 2, 2007
Hi guys,
I was wondering if someone could help? I am using Access 2002 and I am struggling to find out out how you can insert/update/delete records through a form using the design view. Is this possible or do you need to do this another way?
Could some one point me in the direction of a comprehensive tutorial or outline some instructions for what I need to do?
I need to create a form that inserts people's details into a table
When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.
Thanks in advance!
View 9 Replies
View Related
Jan 14, 2004
Hi guys- totally in the frying pan here...I can't seem to get past this initial stumbling block with Access
i've made a simple form that "should" create a primary key from the first two digits of a clients last name and last four digits of their phone number...i've tried an update query button and a refresh data button...the key field gets filled in on the form, but it will not transfer to the underlying table...I keep getting a null value error for the key....any help would be greatly appreciated...
heath
View 9 Replies
View Related
Aug 22, 2013
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
View 6 Replies
View Related
Oct 14, 2004
Hi,
I am creating a league table for my soccer team.
I have a database with these tables:
1. teams
2. league_table
3. fixtures
The Fixtures Table has a lookup where it pulls in the opposing teams - home_team & away_team. There is also Home_goals & away_goals. How can I get the fixtures table to update each teams goals in the league_table table where these details are held.
Anyone any ideas on how this is done?
Thanks in advance.
View 1 Replies
View Related
Jun 10, 2007
Hello!
I am trying to update the current status of an asset, when it was returned or checked out. At the same time, save the changes in a history table to record all the changes in past. I can do individually from different tables and different forms, but I would like to do from one form and one record entry. Is it possible? If so can anybody help?
Thanks
JVirk
View 4 Replies
View Related
May 12, 2014
I have a database which tracks product batches test results and then any reported issues.
I have one table tblOne with [Batch Number] and [Release Time/Date].
The 2nd Table tblTwo [Problem/Issue] at [Time/Date].
What i need to do is get the last batch number from table one which would have been released.
Example:
Batch: 275 Released: 21/09/2012 06:15
Batch: 453 Released: 30/09/2012 07:20
Problem: Wrong Batch Time/Date: 21/09/2012 08:20 Batch:????
How would i update table with correct batch in tblTwo
View 3 Replies
View Related
Aug 5, 2015
I have a form where a user can change the scheduled start date for a job. On a sub form on the same screen is a list of notes relating to that job.
Any notes added, automatically have todays date and are locked when the user clicks off.
When the scheduled start date is changed I need a note to be made. Either forced, then entered by the user or automatically.
I was thinking of making the scheduled start appear in a small form and the button to make it come up could add a new note on the click event (possibly in a message).
Or even better (probably harder) any time the value is changed in the form a new note is added.
Other options could be a pop up form to add one note on a change.
View 4 Replies
View Related
Dec 4, 2013
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
Here's my code:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " UPDATE [tblTicket] SET"
strSQL = strSQL & " ([UpdatedBy], [AssignedTo], [Requestor], [Dept])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & unbEnteredBy & "','" & cmbAssignedTo & "','" & cmbRequestor & "','" & cmbDepartment & "')"
strSQL = strSQL & "Where [tblTicket]![DateTimeOpened] = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"
CurrentDb.Execute strSQL, dbFailOnError
View 9 Replies
View Related
Jul 3, 2013
I am trying to automatically update one table to another each time a new record is added to my form, I have tried using the Update and the Append Query is there another way to do this without using code?
View 4 Replies
View Related
Jun 7, 2005
Hey everyone,
I currently have an Excel Spreadsheet w/ 20 columns and 800+ rows. The columns are such things as Product, Product Code, Description, etc. It has clearly grown to big for an Excel file so I created an Access table from the spreadsheet called All.
Goal - have employees open a form and select a product from a dropdown box and then place check marks next to the info they would like to see for the product they chose.
I created a seperate table called Product, and a query for table All, and a report for the query. I created a form w/ a combo box that lists table Product and has a submit button. I added a criteria in the query under the column product that looks at this combobox. When you run the form, select a product, and click submit it opens a report that shows the results of the query for that product.
The part I cannot get is the check boxes that determine what columns are show in the report. I added a checkbox to my form and then went to my query and unchecked show and added a criteria to look at the checkbox in my form. All that did was erase the column from the query permanently.
I'm sure I'm going about this is the wrong way, but this is my first hack at Access and I'm trying to learn as I go.
thanks for any suggestions,
-Eric
View 5 Replies
View Related
Jan 29, 2006
a a aa a a a a a aa a a a
View 4 Replies
View Related
May 27, 2007
I'm a complete newbie to MS Access, and I'm working on my 2nd database.
Just a small and probably retarded issue: I have a table which includes a field, which contains four numbers. In a query I want my users to be able to enter a number, like 6, and the three numbers after that will have to be wildcards.
Example: The field has these entries:
8243
8184
6423
When the user enters "8", it should show the first two records. When the user enters "6", it should show the third record.
How can I accomplish this?
View 7 Replies
View Related
Dec 1, 2007
Hi Guys
i have created a table and am trying to run a qry that counts specific data, i have been searching but cannot find the answer. I have a standard qry that has the criteria of "staff name" and dates between ## And ##. Now when that information is returned, how do i get a count figure for the results. So for example if there were 15 entries for september it would say september = 15 (ish). I have been told to assign a recordset but no other information was forth coming. Can anybody help with code or qrys for creating a recordset.
Please remember the noob status when it comes to access - lol
Cheers Guys
Anthony
View 1 Replies
View Related
Feb 1, 2006
I have taken over the Access DB from Hell.....
Recently our system crashed and so I fixed compacted and repaired, and all seemed to be good to go. However, next day a bunch of random fields that our reps were once able to add information to are no longer able to add info to. They seemed to be locked out, however, I go into the various forms that were set up and enable is set to Yes, and Locked = No.
Any ideas?
This also effected a Check box field of 4 also.
Thnx for any help
View 1 Replies
View Related
Nov 19, 2006
Hope nobody minds but I don't really know Access and I have a table that needs sorting pretty desperately.I have a table with 5 columns that I need to filter out so just the remaing records are left over.http://img208.imageshack.us/img208/7328/untitled1wb8.th.jpg (http://img208.imageshack.us/my.php?image=untitled1wb8.jpg)The column 'fax' is the column with the records in it and the following columns need the record removing if the box is ticked.TimFPS, OurFPS, OnFPS, NeedsToBeFPS, however the column 'NumberWeHave' I'm not too sure about so I'll ignore it for now (I may need to take them out later)Sorry to be a complete noob and if anyone can help it will be muchos appreciated.
View 2 Replies
View Related
Apr 10, 2007
I have this program that adds information to a ms database file in a very odd manner.i cant change the way the information comes in but is there a way to move it into the correct location in a new table. information is put in as a group of 19 records.
whats happening is this:
these are the colums
primary key,group id,info,info,info,group order,info,info,info
i need to information from 4 of the colums into a new table so i have 19 colums and 1 record.
is this even possible.
Frustrated net admin
View 1 Replies
View Related
Nov 8, 2005
I am trying to make a query in which I want to get the data from the following:
i put in order ID number like this:
=(214)
and I successfully get the data from order id in data sheet view
but I want to get data from 214,220 and 200 at the same time how should i enter in the design view criteria field?
I tried =(214,220,200) but it did not work
thanks in advance
View 3 Replies
View Related
Apr 5, 2006
Hello im new to this and i been working since yesterday on a database. Basically I have 1 table that holds names and numbers asociated with and also the current state. that would be "ACTIVE" or "INACTIVE" and after a week or so (actually after i run a report) should change to "ACTIVE Z" and "INACTIVE X".
Now I have all the querrys and tables and forms and all. but what i want to do is to automate something im doing manually.
I run a querry that shows me only the records that have the state "ACTIVE" and i need to change them to "ACTIVE Z". Now what i am doing is, after running the querry i select all and go Control H and replace "ACTIVE" for "ACTIVE Z".
Is there any way to automate this.
cus i tryecd to do a macro that 1st open the querry and then executes the replace command but that just opens the replace pop up. I still have to tipe "ACTIVE" and "ACTIVE Z" in the fields to replace it.
Is there any way to automate this??
I would apreciate the help
THANK U
View 4 Replies
View Related
Dec 10, 2007
As the title suggests i am brand new to Access (about 2 days). I am trying to create a query that will group the fields according to their names and then add the income for all orders for each individual customer. After that i simply have to sort them. I want to sum the total income per customer and put them into descending order.
Here's a small sample of the list to get the idea:
Customer NameIncome per Order
Sanchez, Cindy$1,326.00
Lizzack, Mark$1,326.00
Patel, Mitesh$1,092.00
Sanchez, Cindy$1,000.00
Patel, Maria$780.00
Mui, Sylvia$780.00
Patel, Mitesh$500.00
And what I'd like the results to be:
Customer NameIncome per Order
Sanchez, Cindy$2,326.00
Patel, Mitesh$1,592.00
Lizzack, Mark$1,326.00
Patel, Maria$780.00
Mui, Sylvia$780.00
Thanks immensely for any help you can offer. I realize there is probably a very simple solution to my question, but after 5 hours of searching the help button, and various online forums, I'm at my wits end.
View 3 Replies
View Related
Jan 5, 2006
Hi. I just started using databases and I am at a loss with some of the definitions. I could do with some help as these are part of my school work! Thanks.:D
What is the role of a 'form'
1.to hold data
2.to provide a 'form' format for reports
3.to provide a tool which sorts the data and prepares it for export
4.to provide a user-friendly data input 'front-end', which can additionally validate user input..
Thanks for any help....much appreciated:cool:
Jane
View 9 Replies
View Related
Jan 26, 2015
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
View 4 Replies
View Related
Apr 30, 2007
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
View 1 Replies
View Related
Oct 30, 2007
Hey all,
I'm an access noob, but decently experienced with Filemaker. I learned quickly that they are two very different animals with such a differing vocabulary, I may be in over my head with even the simplest concepts.
My problem: I am creating a quicky book order database for some professors. I have a value list (drop down menu) that references another table's column of professor names, but would like to create another value list dependent on the professor's name that would list the classes he/she teaches.
Table 1
Columns: Professor, Class 1, Class 2, Class 3, etc
Form 1
a) Professor field drop-down menu/value list populated by values in the table 1's professor column
b) (need) A drop-down list that displays which classes the selected professor teaches.
View 6 Replies
View Related
Aug 29, 2007
Noob alert.
What I am trying to do is write either a 'yes' or 'no' to one of my access tables based on the criteria of a linked table. When I build my query, I input the following into the field:
Expr1: IIf(([5WellInformation]![LowPH]<5.5) Or ([5WellInformation]![HighPH]>10) Or ([5WellInformation]![Temperature]>75) Or ... , "Yes", "No")
My query type is 'Append Query'
The problem I am encountering is that I require 39 different criteria to come up with either a Yes or No. If anyone of those 39 criteria fail, than a Yes is written.
When I place the entire expression into the query, half of it is cut off because the expression is too long.
Any Ideas? Am I doing this right... or is there a different way I should approach this.
Thanks in advance to anyone who can help!
View 10 Replies
View Related
Dec 11, 2007
Hey everyone,
I'm new to access, I have been using Excel for quite a while and I am familiar with VB and macros.
I have never been much of a book learner, mostly hands on, which means a lot of reverse engineering to figure out why/how things work.
I have a scenario, and found a DB that had some functionality that I liked and I had reverse engineered it to do a lot of what I want.
Where I need some guidance:
1. on my opening form (Clients) I would like to be able to type in Client Number and have it return the correct info.
2. in my Who Has It is there a way to do data validation (or dropdown box) I mean I only want them to be able to enter valid names not crazy stuff like MickeyMouse.
Thanks a bunch!
View 5 Replies
View Related
