I'm having difficulty updating an existing record in my form. I can enter a docket number on my form and when i hit my button (Find Docket) it brings up all the customers info (name, date, invoice, vat, total, etc.) What i want to do then is be able to amend/update the record. Just wondering do i need to put vba code in my save button and how to code it and is it similar to my find data code below.
Here is my save function code:
Function SaveData() As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Skips Delivered")
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset Then it uses the rs.Findfirst method It checks if rs.Nomatch is not true (so the records exists!) Then it starts updating the record with rs.edit rs!value1 = myvalue1, rs!value2 = myvalue 2 rs.Update There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
I want to open an existing record via a form and then edit that data but save the form as a new record. Basically a form used for handover/takeover process where most of the data remains the same but the new form is the basis for the next handover/takeover.
And I can display any existing question and make updates to the question and answer, that works fine. BUT, what I want to do is update which category a question is in. If I change the Category in the form, it updates the 'Category' in the Category table. What I want it to do is update the CategoryID in the question table.
Combo box on continuous form should have the control source listed as the field on the form that will be updated. The Row Source, however, is a query that includes 2 things: the field on the form that will be updated (this part will be nonvisible in your form) and the table/query of selections you want to show up in the combo box (visible). Then ensure the Bound column is set to 1 and the Column count is set to 2 with Column Widths as 0";1"
I have a navigation tab with 6 sections, of the 6, 5 of them are self-made split view with a form view on top and datasheet on bottom and upon adding records through a command button on form view I would like the datasheets to update.
On other forms it would (I believe) update to the bottom of the datasheet, now on the ones I've been banging my head against it either replaces the top record or doesn't show up unless I switch tabs and switch back. From my searches I assume this is in need of a requery...
I have the employee data record... I want copy of the same record to insert in table but with different employee id and Site through forms. StaffNo is the primary key.
First Access 2013 form being created. (i may not use proper termonology but trying).
Tables: tbl_Clients (PK=clientID auto#)-holds details of each client (Lname/Fname/MName/addr/preferences etc) tbl_Encounters (PK=encounterID auto#)-could have many encounters for each client. tbl_followup (PK=followupID auto#)-some encounters need followup, some don't, some encounters have many followups.
Have a form linking to tbl_Client, with subform to tbl_encounters (going to create another form for followups-haven't got that far yet). I would like to try to avoid duplicate clients in the tbl_Client, so when the form is opened they could look for client if not there add new. However for the existing client if say the address changed i want them to be able to do that. (i could get rid of dups in a query/report based on matching certain fields etc, however i'd like to avoid it in the first place).
I am very new to VBA and I have been self-learning VBA from two month and have been assigned to new project Work Authorization & Lock Out Tag Out.I have table called tblWA & tblLOTO.Basically most of the LOTO/s are associated with WA #, following example
WA # in tblWA Associated LOTO/s in tblLOTOs 25258 123456 123457 123458 123459 25259 123410 123411 123412 123413
However, both tables are in relationship right now..I have form called WA Switch Board where I have Datasheet View form with all WA records, fields are WA#, WAStatus, WAIssuedDate, IssuedBy, CompletionDate and etc but I have dropdown with with WAStatus with (In Field Today, On-Hold, In Approval, Cancelled, Close).Now from the main switch board when authorized people try to change the status of permit to Closed I want recordset or count to loop through tblLOTO and give me a message box if associated LOTO/s status not equal to Close.In other word, if the associated LOTO/s are not close then the selected WA # in WA Switch Board cant close.Here is the code I have been playing with no success
Private Sub PermitStatus_AfterUpdate() Dim db As Database Dim rs As Recordset Set db = CurrentDb
I have a database where I need to append part of a new record to another table. But when I open the query with the link to the form that is open I cannot see the added record until it's saved to the open form. I tried to re-query the key ID field but that does not work. How do I do a re-query without the form requiring everything and then I am on the incorrect record?
I have a database which i am constantly updating and improving. I have a few friends who use the database in a current form. If i made an update say to a form or to some of the coding to make the DB more efficent, how can i update the dBs my friends are using without damaging or changing the existing data, or migrating it to the new design.
I have searched the forum but have failed to find the answer to my problem. I have a front end ms access 2000 solution that I distribute to user PC's with an MDE back end data base on a server. I now need to release a new version that includes changes to forms, queries and tables. However there is data in the original mde data base I need to retain. Is there an easy method to migrate that data to the new data base. I have changed some relationships but this should not affect data integrity - most change is related to adding new fieldsto existing tables or new tables (no previous data). If I create a new empty mde will I be able to import old data into it from previous mde?:confused:
So, I have two forms that I am trying to make work the same way.
With F1Entry I can use the combo box in the header to select different request numbers.
With F2Finance I cannot do this. It works if I set the Form Record Source to T2FIN, but when I try to Add Existing Field, something about selector combo box breaks and I cannot select different request numbers.
I currently have a database that is missing various pieces of information, we are getting a temp in to complete this. I don't want the temp adding data directly into Access so I have exported the table that needs updating into Excel.
How can I then import the new data without altering current data.
I know this is no problem for new records, but if I have a current record with a few fields of information missing how can I just import information into the blank fields. This can't be a manual process as there are hundreds of records.
I am in the process of building Append Queries for new records, and I know I can do an Update Query to enter specific information. However, how can I update multiple records from a (externally sourced) linked table to fill in blanks of an existing table? I have created a query to identify records with 1 or more matching criteria which contain the blank fields. I now want to update those blank fields. The data in each blank field is different for each record (same type, just different data).
I have a table with more than 60 fields and need to update it with records from another Access file with a table with an identical data structure.
Is it better to run an update query or an append query (i would have to delete the original records in the target table first) or a union query?
If I run an update query I will have to manually add each field to the query.
The update query will not add 'new' records. If I run an append it is quicker because I can use the * to match all fields, but i will have to delete the 'old' records first, as both tables use autonumber for the PK so the PK ID will be the same in each table (will get a key violation error).
If I import the 'new' table and run a union query it will match the fields and add the new records, but then i will have to create new table from that query.
Is there any way to update the same record from two forms? The requirement is that in a form I have a checkbox. If I select the checkbox, it will open another form, in which I can update some fields which are of the same record. When I tried, it is updating on another record. Is that possible to do so?
i am attaching a sample db . If anyone knows how to do it, please update the sample db or give me advice.
I have a form (frmHourEnter) with a textbox (Week_No_Calc) which has a calculation, in Control Source, based on another textbox. After exiting from Week_No_Calc how can I get a field (Week_No) in table (tblHourEnter) to be updated with the calculated value?
Each month I receive a new excel spreadsheet (alpha Roster), Last month I created a form so that I can track certain things off of the Table (spreadsheet), I just received the new Spreadsheet for this month and was wondering if I can use the existing form and how hard it is to link it to the new spreadsheet? Is that even possible? I don't want to have to re create the form every month, I would like to just bring the new alpha roster in and be ready to work.
I'm having a bit of a problem with a database I'm trying to create, what I am trying to do is create a database to handle jobs in an IT workshop. The idea is that you generate the job card and then at the bottom you have a section to enter in the parts that are required for the job, and then have a report that prints out a list of all the parts required for all the jobs under a heading of the job card number.
The problem I am having is how to add this sub form / table to the job card sheet. I tried putting in a sub table, but that wont let me make additions to it, and I tried putting on a form but that wont retain more than one entry per job card (I have put it on continuous form).
I have read where you can drag fields onto the tab pages to create a form or you can place a subform onto a tab page, but can you drag an existing form onto a tab page?
I have done it and it is working, but I just wanted to know if this is ok to do or would it lead to problems.
I currently have a query that a subform uses to display some results. In this case, there are 3 columns displaying the name of a sandwich, the ingredients, and the number of servings of each ingredient. It looks like this:
That is the current output to the subform. The SQL statement I use to generate this list is here:
SELECT [TBL_Sandwich].[SandwichName], [TBL_Ingredient].[IngredientName], [TBL_SandwichContent].[SandwichTotalServings] FROM (TBL_Sandwich INNER JOIN TBL_SandwichContent ON TBL_Sandwich.SandwichID=TBL_SandwichContent.Sandwi chID) INNER JOIN TBL_Ingredient ON TBL_SandwichContent.IngredientID=TBL_Ingredient.In gredientID WHERE TBL_Sandwich.SandwichID=6;
I have bolded the last item the 'SandwichID'. It is this number I need to change dynamically based on a button being pressed on the main form. Essentially they enter a new sandwich, put the ingredients in, save it io the database, and I save the servings for each item as 1. Now I'd like the new sandwich to be displayed in the subform using the new ID of the sandwich which was just created.
If I try to put this long SQL statement in the RecordSource property I am told it's too long for the property to handle so I can't set this property dynamically. how to get the subform to requery with the new ID number of the item I just generated. I can get the ID number via DLookup, that's easy, but setting the subform to use this is my problem.
I have a tab control with a Counsellor Training tab on it inmy form frmCounsellors. The issue I am having is that when I select a training type from the drop down in the sub form on the tab control on the counsellor training tab and then go to a new record of the main form, not on the sub form, the training type I selected on the form stays the same. If I change it on the second record, it stays at whatever I changed it to, even if I return to the first record it stays at what I changed it to as well.
How do I get it to stay with the counsellor and have it zero out for a new counsellor and it to allow me to enter something different for the new counsellor then save it for that counsellor?I just realised, I am having the same issue with the location combo box but I am sure I could apply a fix to both boxes if I knew one.
Ok so ive built a form that a user can use to enter in data that will update a certain table.
The table has for fields: Email, Password, Active and ID.
In my form ive got rid of the active and ID fields as i want them to update automatically when the user fills in the email and password and hits the submit button on the form.
The only problem im having is getting my head around how i would update my other two fields once the user clicks submit?!
i was thinking that i could simply code the submit button to add the relevant info into the fields eg onclick active = 1 and ID = previous id +1 but im completely lost
on my form i have a few radio buttons, check boxes, text fields and combo boxes. i am able to change and update all fields except combo boxes. it modifies the wrong table...
combo_table ------------ id value 1 house 2 school 3 work if on my form the combo box for a specific record shows house selected, the value of 1 (the id from combo_table) should be the value put in the main talbe...if this is changed to option 2, school, the main table should have 2 in that field...
what happens though, is if i change from 1 (house) to 2 (school) combo_table ends up looking like the following:
combo_table ------------ id value 1 house 2 house 3 work
