I am working on a DB for work. Currently the form has 3 options, each one selected hides/shows different fields pertaining to that option. Works great when you are putting data into the tables using the form. Problem is when you start that the beginning and click 'next'. When clicking next you do not see the fields show or hide. How do I make this happen?
View RepliesI 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?
I am trying to UPDATE records from the Master form by clicking a button to a new window -
frmCustomerMaster (main form) - a button will trigger to UpdateCustomerMaster new window and pass all the data to the new window.
UpdateCustomerMaster (New window Form) On Submit Update values.
frmCustomerMaster (main form) - is currently locked by properties, I do not want main form to be editable.
how to pass the data to new window and how to update the records?
I have a pretty normalized Access Database. The table that I am trying to add new records is tbl_returns and has 4 fields: return_ID, serial, reason and inv_num.
When I sell a card (or a range of the cards) an invoice including all the information is saved as a record in a new row in tbl_invoices.
If a vendor wanted to return a card (or a range of the cards) in the next visit (weeks or months later) I will accept and in most cases they want me to switch the cards with new ones. Therefore in a new invoice (different date and invoice number) I will give him new cards and return the cards that he wanted to change or return.
Now I have to assign NULL to the inv_ID field in tbl_allPins in order to make it available for sell in future. At the same time I want to have a record of the returned cards including serial number , the reasoning of return, invoice number and/or a little note about each one/range of the card(s).These are to be recorded in tbl_returns as you can see.
For instance you want to return serial numbers between 9876 and 9880 (includes 5 cards) because of the "scratch off problems" and your invoice number is 22222, using frm_returns. After you process it and then open the tbl_returns to check the result, you will see 6 records are added instead of 5. I was able to understand why it is happening (I believe so!) but I could not fix it. Also I cold not write a VBA to remove the inv_ID in front of the related serial numbers in tbl_allPins.
Also in another trial was ended up to creating the Form1. Form1 looks better (has no extra records) but I have trouble to navigate through the records in tbl_returns. There was a sub-form added but it was showing all the records in tbl_returns which is unwanted.
By the way, serial numbers and PIN numbers are each a unique number in tbl_allPins.
Still working on the registration and badge print system for their upcoming exhbition the company I do a bit of work for. Managed to solve most of my problems but have got stuck here.
Basically each of the badges have a barcode (code 39) on them which is in the following format...
*1000000123$M*
(*'s required for the barcode to work, the numbers are the badge number and the $M is the enter command (i think))
Basically we need a form that allows us to scan the barcode (the number of the barcode is the Primary Key in the tbl_Attendees), and for this to update the "Attended" (Yes/No) field to yes. This bit I can do with a simple update query, but we need some form of confirmation, something simple like a line of text showing up on the form saying
"Joe Bloggs of Leeds registered as Attended".
I'd like to run an Update Query on a limited number (or percentage) of records from a Button on a Form.
[URL] .....
But instead of opening a Form, I want to Run an Update Query instead.
I need to know the best way to append,
table1=productid,ProductName,Qty to table2=Productid,productName,Qty,date daily
I want to be able to append Records daily from table1 into table2 OnClose. But i do not want duplication of record in the same day.
If changes occurs in table1 after append,table2 should be updated using If conditions
There are around 100 customers.And on application open, need to check if customer has paid invoice. If the customer has paid/not paid need to change the status accordingly. Form on open event changes status of only the first customer.Form on current changes status only if we move to the specific record. Is there an event to trigger to check the entire table records on application open?
View 9 Replies View RelatedI am new to Access. I am after a vba script to run an update query.
I have a table named MYOB_Invent.
I have a Query Named MYOB_Update.
The query is showing records where my field named Test1 is null.
I want to update records with null in the field to today's date (the date of the day that it is run) formatted as 18/08/2014 and the field next to it is named Updated (which is yes/no) from un-ticked to ticked for the updated values.
I would like a vba script to automate the process as I want to automate it via a commandbutton for users.
How to restrict the updation of a record while entering until a save button is pressed on the form ???????? is it possible without the vba ?
View 10 Replies View RelatedMy membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.
The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.
Browsing the all of the existing records is fine.
I get an error "update or cancel update without add new or edit" which seems to point to this code.I am using MS Access 2010.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Store when record was last modified and by who.
'Initially stores when the record was entered.
If Me.NewRecord = True Then Exit Sub 'Exit if new record
Me.DateLastModified.Value = Now()
Me.LastModifiedBy.Value = getUser()
End Sub
Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
I have a user login code am using for users to login and only access their records in a subform according to their user ID and access level (user or admin). On the main form I have a user name field, the password field and two other fields: one to hold userID and the other to hold access level.
I also want if the person logged in has "Admin" access level the subform should return records from all users. For now what is happening is that even when I assign myself with "Admin" access level, the subform is not returning all records from other users, but my own records only. The original demo where I found the code worked well but I cannot find it and I am totally lost on this.
Here is the code I have so far:
Code:
Private Sub cmdLogin_Click()
Me.Hold_User_ID = Nz(DLookup("User_ID", "T_Users", "Username='" & Me.UserName & "' and pword='" & Me.PWD & "'"), -1)
' check to see if we have a good login
If Me.Hold_User_ID = -1 Then
MsgBox "Invalid username or password."
[code]...
Erm, I know this sound a bit silly....but I woudered if there was a way of adding anew record to a table using vb code?
My table is called tblTransmissions
The fields are
TransmissionCode, AutoNumber
TransmissionDate, Date/Time
TransmissionTime, Date/Time
TransmissionStatus / Text
TransactionCode / Number (Linked to another table by One-To-Many)
When I hit a command button, I would like to update the recordset by firstly adding a new record. Then set Date & Time to Now, Then TransmissionStatus to a control on a form, same with the Transaction Code.
Normally I would place the values on a form and update the values that way, but just wondered if this was possible!
Thanks
I am chasing a quick solution to what I am sure is a simple thing.
In my database I have a table of items that have to be held for different periods of time (one item type kept for 5 years, another for 3 etc). In another table, linked to the first through the item type, I have dates entered against records which, using the first table, will calculate the date I can get rid of the item.
My problem is that the period of time for some items to be held has now changed, and I want to change all the relevant records to update to the correct disposal date.
I have tried just changing the period items are held for (in the form), and that will provide the correct date for future records, however I want to refresh the old records as well so they are all correct.
Is there an easy way to achieve this without going through each individual record.
Hello all...
What I am doing is attempting to update a table through a form. I have been reading up on update queries, but I am not quite understanding how to use them. I have a table containing consumables that we constantly issue and receive. I have a form for each of these functions. Example: I order two ink cartridges from a vendor through a purchase order. When they arrive, I would like to enter the qty. I am receiving, then have it automatically add it to the table value. I don't know where to implement the arithmetic. Also, for PO's with multiple items, what's the best way to update the table with those?
Thanks in advance...
The problem is I removed my primary key (if you can even use a primary key for this) and there's no record number option on the query. I just need 800, it doesn't matter which 800.
I was thinking of adding a field with a series of unique identifiers, but I don't know how to autofill sequentially.
Hi all I have a table "tblEmployee" which has "name, employee_no, manager, etc" I recieve from HR a updated spreadsheet which can have new employees or just updates. ie new manager name. I can run an update query to update changes based on the employee_no which is constant. How can i add new starters from this list ?
View 2 Replies View RelatedI am a new user to MS Access and i'm creating a stores account for my works, however i'm having problems with the updating of records after colleagues have been issued with kit.
I have three table (employees, kitlist and issuelist) what i want is that when i issue kit to employees this updates the item in the kit list to reflect that i have issued an item, ie. take the number away from the units in stock. This is probably really easy however cannot find a solution to this problem.
Thanks in advance for help
Hello -
I have a couple questions I hope that you can help me with. I have an asp page that is retrieving a bunch of records from an Access Database based on a field value (in my case its based on Department). They are then placed in a table format in the asp page. The user can then modify one or many records. Everything was working fine and then we upgraded our IIS Service from IIS5 to IIS6. Once we did that I started getting errors. I was able to update certain departments and not others. This told me the code was good and that the database permissions etc were good. It has to do specifically with the value or similar. I looked further and came to realize that it appears there were to many records to update. The Departments with a few records updated fine. THe ones with 500-1000 were erroring out. I went into the database and deleted the records from 1000 to 500 and everything works.
Does anyone know if there is a record limit to update?
Is there a function on ASP that I can use like Me.Dirty that will search first for updates and then just update the single record?
Is this a result of IIS6?
Here is a bit of my code: This is the area that it is bombing out on....I am not an expert and believe I might have hit an ARRAY size limit. Does that make sense?
Is there an ARRAY size limit?
THanks in Advance
arr_ids = Split(Request.Form("Auto"),",")
Hello friends,
I have a table with a SSN field. Data is shown as a text with the following format:123456789.
I would like to run an update query to change the format to:
123-45-6789. Any help? THanks.
I'm relatively new to access. I probably use about one tenth of one percent of what it has to offer because I have trouble understanding it at this time. I need to know how to update a yes/no object.
View 2 Replies View RelatedI need to update a field with a Sum() on event.
View 12 Replies View RelatedI've recently come across a problem whilst saving an edited record..I get the following message but only sometimes..could not update currently locked by another session on this machine..Sometimes the record saves without a problem (mainly on the first edit). However, any further edits of the same record mainly but not always triggers the above message. I can't work out why it's happening, It's a split database but I'm the only user on a standalone PC (so no other users editing the same record). Sometimes I get lucky and the record save even after the first edit but not always. There doesn't seem to be any pattern.
Record locks for the form are set to NO LOCKS
Recordset type is set to DYNASET
In settings -
Default open mode it set to SHARED
Default record locking is set to NO LOCKS
Open database by using record-level locking IS CHECKED
This has been working fine for years so I'm a little stumped as to why it should happen now. The only thing I've changed recently is a memo field from Plain text to Rich text.Whilst writing this I just went back and double checked and found that if I change any other field (not the memo field) the record saved just fine. So the problem is to do with the Rich Text memo field only.
In access 2010 if there is a table "log" with
Item Amount
Water 100
Egg 200
and another table "setup" with
Item Amount
Water 20
Egg 50
Can the "setup" be used to update the "log"
Water 80
Egg 150