I have the following script which updates fields in a table.What I am after is a msgbox script to list the ID field value once it is updated.
Code:
Set rec = dbs.OpenRecordset("Select * from Stk_Merge_Delete_Register")
rec.AddNew
rec("StkItemID") = [Forms]![MergeItem]![txtStkFrom]
rec("RackID") = [Forms]![MergeItem]![txtRackFrom]
[code]....
I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?
Can this be done? because if i use find it will only search the filtered form i have onload of the form?
My onload event is based on fosusername()
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?
Hello all:
Posted earlier in General, this form might be more appropriate.
Every week I download new information into a table. The download has all the information, but the table will spill out any information from the download that is already existing in the table. The downloaded data does not have a date criteria which would have been easy to keep track of the last data download.
How do I create a query right after the download, that will provide a list of the new information just downloaded.
Thanks for all your help!
i am trying to have a continuous form where i have a combo filter to filter the records. i would like to be able to add records on this form but i keep getting an error
Field cannot be updated
runtime error 3348 cannot add record(s); join key of table 'tblOrdersItems' not in record set.
the table system for the relevant tables is a one to many.(parent / Child).it seems to work beautifully. i have the unique id fields of both tables in the form. i have the foreign key of the parent that is bound to the child form.
when i create a new record the error message appears. when i click end. i can see that the id fields have been populated and they look as they should. i cant seem to grasp what join key is not present. open frmDiaryNoneItems. there is a combo box in the header that you will need to toggle to show some records. try and create a new record and you will grasp what i mean.
I am working on a database and i have notice after making a form that when i enter a record using a form if i enter incomplete data on the form access automatically save that record and generate a id for that record.
For example I have a table that contain
StudentID,StdName,FatherName,DOB,Adress,Phone
And I have created a form for that table that also containing these fields.
Here I want to do that on the form I want a "Save" button , and the purpose of this button that when I click on this button then MS Access Save the record and then generate the ID for that record and if i close my form without pressing "SAVE" button access do not save that incomplete record.
I have Field "BC1Chng" which requires user input. I want to be able to write a code to reference to "BC1Chng" if there is an input in that field for any record...I want to copy the Remarks into each record in the Remarks Field.
I was able to get the remarks1 field to loop through each record copying what was in that field into each record.
Now I want it to look at the BC1CHng field and only copy to remarks1 field if there is any input in that field???
Using Windows 7,
Access 2010.
Is there a way to combine four fields into one field in the same record? I am trying to build Equipment Code field by using the abbreviated values of Category, Item, Type and Dimension fields.
Example: if Category=Storage System (SS), Item=Wire Deck (WD), Type=Flared (F) Dim=42x52 are the user selected values the corresponding equipment code field would be: SS-WD-F-42x51 This is done in order for all users of the database to build a proper code in the correct format.
What I have so far: I have the four fields and the equipment code field setup in a form, the equipment code text box has a control source of: =[Category] & "-" & [Item] & "-" & [Type] But when I select values of the four fields and the equipment code is populated I try to save the record and I get an error stating that the Equipment code field needs to be filled out. Three fields (Category, Item, and Type) are look-ups from other tables and Dimension is typed in.
I am trying to run a sql statement in vba in which it selects all the records in a table related to a order Id number. I then want it loop through all the records and check for a check box which is selected. if its selected then "A" variable becomes "1" and i will then run a if statement saying if variable is 1 then run this code else run code b.
The problem I am having is that if there are 4 records the code checks through all of the 4 records and keeps changing the variable so
record 1 variable is 0
record 2 var is 0
" " " 1
" " " is 0
so although the records for a given order number does have a checked box the code wont recognise that.
What I can do or what code I can add my minds gone all blank on this.
I have a form wich users input information into and it goes in one table. Several buttons on my form send a report from that table to people.
What I need to to is that every time one of these buttons are pressed and a report is sent, a timestamp and wich button was pressed needs to be recorded in another table in the same database.
This is because I need to record to whom each report was sent and when.
I have been using my db for 3 years without this particular problem: I am now unable to add records to one of my tables - either through the form or in datasheet view. I have a patient table and a visit table. I put in the patient data using a form and then go to the visit form to add all of the visits for the month. I have never had a problem adding data to any of my tables but now I am unable to add data to the visit table. As much as I didn't want to I brought up last month's back up to enter the patients again. Before I did I checked to see if I could add visits - I could not. So I checked January's back up - same issue. I made no changes to the db before trying to add a visit .
View 10 Replies View RelatedI'm setting up a college database. On my enrollment form i have the following fields
Enrolment_ID
Student_ID
Course_ID
I'm receiving the error in my Student_ID field. It's telling me a related record is required in my Student Table.
The drop down shows the student name/date of birth/Education number
But if i set it to just show the ID it works fine, is there a work around for this as it will make life a lot easier for the dropdown to display the student names rather than the ID?
The dropdown is based on a query i have of accepted students....
I'm using Dmax() to return a max number in a field which I then want to inc by 1 for a new record. Dmax is returning 999. I believe Dmax therefore thinks it's a text field. So where do I change this to a number field?
View 2 Replies View RelatedI am trying to get a record from tbl_new_accounts to tbl_closed_accounts, based on the number entered in a input box.
Code:
Dim StoreNumber As String
StoreNumber = InputBox("Please Enter The Store Number", "")
CurrentDb.Execute ("INSERT INTO Tbl_Closed_Accounts SELECT Tbl_New_Accounts.* From Tbl_New_Accounts WHERE (((Tbl_New_Accounts.Fld_Store_Number)=" & StoreNumber & "));")
I need to add only one record into a database by using a mask.
For sure when I call this mask via a button, this new record has to be added after the last one.
Someone is suggesting me to use function docmd.openform, but I really dont' know how to do it.
I was trying to further my knowledge of access. I have a database that is suppose to be used a s a simple budget database.
Form: StartingAmount
Table: StartinAmount
Field: StartingAmount
Button: Create
Form: Transactions
Table: Transactions
Field:
TransactionDate
TransactionName
TransactionAmount
When the database is open the form StartingAmount is open (only until a starting balance is entered.)
User then puts the starting amount in. clicks create.
Then a record is added to Transactions.
TransactionDate = Date entered (auto populate)
TransactionName = Starting Balance
TransactionAmount = StartingAmount
I tried this code:
DoCmd.RunSQL "INSERT INTO Transactions (TransactionAmount) " _
& "VALUES(" _
& "'" & Me!StartingAmount& "') "
With this it kept giving me an Append error and Im not sure how to get it to stop and how to add all the fields needed.
I tired to do:
Code:
INSERT INTO Transactions([TransactionName], [TransactionAmount])
VALUES ("Starting Amount", Me.StartingAmount)
But i keep getting an expected error in the VBA side.
I have several tables that are linked that I need to be able to add to. This is an example of the structure:
Patient Info
ID
FK_Ward - ID of Ward table
FK_Room - ID of Room table
FK_Cond - ID of Condition table
Ward
ID
WardID
Room
ID
RoomNumber
Condition
ID
Description
Unfortunately, its not the exact one as I can't post that here! What I am trying to do is to add a new patient record using information from the other tables as guides - e.g. the user selects the name of the Ward but the ID in the Ward table is entered, the User selects the number of the room but again the ID in the Room table is entered, and for the Condition same thing. So I would like to have dropdowns (or ComboBoxes) for each of these and based on the user's selections add a new record to PatientInfo using the ID values as links.
I need to export/print every single record from table as a pdf file (one record one pdf file). how to do this.
I'm working on Access 2000.
Using access 2003, i would like to add a button to a form so that when clicked all the data in that record is moved (cut and paste) to another identical table, like archive.
View 5 Replies View RelatedI need to modify my customer table to cater for multiple names at one address.
Should I move all the addresses into their own separate table or leave them as is (name-Address-postcode- email etc in one table) and put the additional addressees into a separate table or is there a better way I have not thought of.
I have a table that deals with current data (as in member rentals of items). The normalized tables that we have been given include a rental history table. The idea is that, when the member has returned the product a history entry is made in the completed rental table.
I wish to have a button which is clicked to triggers this event. Would I program a macro to do this? In other words, would I create an event that passes those values to the relevant fields in the other table (Name, date issued, return date etc)?
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.
After I enter data into one field in a record I would like the form to save the record when I move to another field in the same record. It seems that the record is only saved when I exit it entirely. Is there a way to save a record when moving between fields in that record? Can this be done without using an Event Procedure for each field?
View 8 Replies View Related I need a simple audit capability
I have a table (Called Master Active List)
I have an empty duplicate of it called History of Master Active List. (Identical fields, with the addition of a distinct ID (primary key)
I have a form where the master can be changed (Called customer details)
When any change is made I want to post the whole record to the history table
1 - Am I correct I no longer need a form based one,(office 2013) and can do one on the data table itself?
I want to capture everything, can this be done with a wildcard, or do I need to go line by line?
The master table has a ton of fields.
I have two tables that need to be exported on one .txt file.
One table has the header record. And the 2nd table has the detail record.
My issue is as follows:
1. Need to create one .txt file
2. The header record has one set of export specs and the detail record has a different set of export specs.
3. The first line is the header record, and 2nd line is the detail record.
Example below:
H Abraham Armenta January Cake 00123
D Father Saturday 02536 00123
Both tables have a common control number - in this case represented by the 00123. I just cannot figure out how to accomplish this..
I am trying to create a duplicate of a certain record in a table so that the user can then edit the new record - or old record even as it doesn't matter - as long as one of them is original. The idea is that any revisions / changes are stored the user may of made.
View 2 Replies View Related