Modules & VBA :: Running Update Query On Record And Have Form Show Updated Record
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?
In one form, I have to click on button that opens the other form. Each form is bound to its table and those two tables are linked by Primary [RaDAR_Id] and Foreign [radar_id] fields. I would like the code to:
* open the other form * move to the first record that has [radar_id] = [RaDAR_Is] if exists
If there is no any record in the other form that contains [radar_id] = [RaDAR_Id] then I want to create such record. So I am using the code (below). The problem is that the other form is not being updated.
Code:
Private Sub cmd_edit_usage_Click() ' DoCmd.OpenForm "frm_edit_usage" Dim lngRadarId As Long Dim patientId As Long Dim rsUsage As DAO.Recordset Dim rsUsageWrite As DAO.Recordset
I have created a form with two subforms within it.
The TOP subform enables the user to view a specific record (not change it)
The BOTTOM subform enables users to view a datasheet form of all the records and amend them where necessary.
I want the TOP subform to automatically go to the last updated record, meaning that if the user changes the data of a record in the BOTTOM subform it will automatically display the last record on TOP that was updated in the table.
I've looked at the macros available and it only allows me to 'GoToRecord' to either Last, Previous, New etc... Is there any way to go to 'Last modified in the table'??
I have a query that I would like to run from a form. I have created the button and the query, but want to add in a clause that will use the user selected record / field as the criteria for the query when its run.
So I have a form which is showing the current record and you can scroll through these and make modifications to them from the form as opposed to using a table.
The form has the following fields populated from the main table
Customer name (Can appear more than once) Status Date Servers Positions
In addition it has several buttons for next record, previous record, first & last record, new record, delete record and update record.On this form i have a text box that i want to be able to use to search for a record using the customer name when you press the search button associated with it. I've got some code and it is mostly working but it seems to be moving the text entry cursor to the date box of the record for some reason.
What I want it to do is take the text from the search box and find it and move the form to that record.
Code: Private Sub btn_cstmr_srch_DblClick(Cancel As Integer) On Error GoTo HandleError Dim strFindWhat As String strFindWhat = Me.txt_cust_search.Value
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.
I am building a Inventory Management Application for Tyre Shop. I have SaleMainTbl and SaleDetailTbl both used for preparing daily sale summary. I have Mainform based on SaleMainTbl with TxnDate and Total Amount (Sale) and the TxnDate is in one to many relation with SaleDetailTbl. FormSaleDetail is multiple row(continuous) form that makes billwise summary of each day having -TxnDate--BillNo--ItemSold--Company--Qunatity--Rate--Amount fields. I have inserted this form in FormSaleMainTbl.
So FormSaleMainTbl is Main form and FormSaleDetail is subform. TxnDate in FormSaleDetailTbl is automatically taken from SaleMainForm. I have further added text boxes in Main Form to show company wise sale for each day for which there is a query build one for each company that takes the currently loaded date from FormSaleDetail and calculates the Sale (Sum) of each brand (Company) of Tyres. All these objects are working very fine. However I have to close the MainForm and reopen it for result of query to appear in the appropriate text box in Main form.
Is there way to do this as soon as record is entered or at least at the end of completing the entry of each days sale transactions without closing the form. So the gist of the problem is realtime display of query result in text box on a form or updating the form screen immediately on updating any record or at the most after completing the updating of form but without having to close the form.
I have a main form called "frmMain" and on it I have a text control named "txtBoatStatus". The control source for txtBoatStatus is determined by code that I have in the Form_Current event. The code is as follows:
txtBoatStatus.Value = "There are " & BoatStatus & " boat orders that are complete."
(end code sample)
Now when I first open the frmMain everything works great and the control will say, "There are 5 boat orders that are complete." Perfect. However, In the instance if I open the form with my Boat Orders and mark off another order as complete (bringing my total to 6), the txtBoatStatus does not update when I reopen frmMain. It will only update and show, "There are 6 boat orders that are complete." if I manually hit SHIFT+F9.
How can I do this automatically? I've tried various things such adding "Me.Recalc" or "Me.Requery" to differnt events, controls, etc. but I still cant get it to show the updated number automatically. How can I get it to update without SHIFT+F9?
Is it possible to run a query/filter in order to hide/show a field in a record instead of it affecting the entire record.
What I'm trying to do is, in a form the user can enter information about a service call and they can enter a recommendation for the next visit. When they enter a recommendation they are able to select which month the recommendatin should pop up. This Service report get's printed on a monthly basis as a report. Now the recommendation field should only pop up if that particular month which was selected earlier is true. I've tried different methods, but it would affect the entire record as opposed to just one field.
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?
Is it possible to have the control tip show related records from another table?
I've been looking around the internet, and I've seen many examples for showing concatenated fields on current record, but had no luck with what I'm trying to do.
For example.
I have a continuous form, and on it shows top line data. Where a record in this form shows as "Split" it means I have more data related to this record on another table.
Is it possible to show however many fields are related to this record, when I control tip, or mouseover?
I've been playing with
Code: Me.textbox.ControlTipText = Me.textbox
but was wondering, if this is possible, how I'd reference the other table,
If said column of continuous form is "split"... lookup related table by ID number show however many columns of data..
I was creating a program using ms access. consist of 2 table of database called employee and userlevel.. in employee table have ID, name, username, password,userlevel, and others related employee profile.. and in userlevel table, it consist of admin level and normal user level.. Userlevel table allow to differenciate admin and normal user(employee) .. in my program each employee having their own loginID and pass to access their own data.. the problem with my program now is displaying the data that belong to the logged in employee. its mean that it only show the data that owned by currently user that logged in..
I have the listbox named "payment_date", which shows recordset specified by following code:
Code: Private sub Form_Current() Dim que As String que = "SELECT [date] FROM payments WHERE id Like '" & Me!myid & "*' ORDER BY [date] desc;" Me.payment_date.RowSource = que End Sub
I need my listbox to show recordset with record count like this:
I have a combox field on subform C that shows records from a query.How do I get this combo to only show records that relate to an ID on subform B.I have a subform for storing contracts and these are stored against a company name.On subform C I have a field that currently shows all records in a table. I need this combo to only show the records that relate the current record on subform B?
I have a form that when a user fills out the information and select a submit button. It brings up that record in reports in print preview for them to print. The issue i am having when the user enter the information, the report does not show any data until i refresh it. I tried include me.refresh,etc. No luck. I attached the code to make things simpler.
Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Dirty Then If MsgBox("Do you want to Submit this Contract Form? Clicking No will DELETE ALL ENTERIES,and Log You Off", vbYesNo, "CONTRACT FORM") = vbNo Then Me.Undo Cancel = True Else If MsgBox("PRINT FORM", vbOKOnly, "CONTRACT PRINT") = vbOK Then DoCmd.OpenReport "rpt_Contracts_Main", acViewReport, , "[CONTRACT_ID]=" & Me!CONTRACT_ID End If End If End If End Sub
The reports comes up, but i have to manually refresh it to show the data that was enter.
New member, so forgive me if this has been covered before:
Trying to build a Business database (more for the exercise than any desire to run a business) and have the following problem -
Have a Purchase Ordler Line table and an Item table - the item in the PO line is a lookup from the Item table - I want the PO line price to default in from the the item table.
Created the query and generated the form & subform - on screen, the substitution works, the PO line price does get picked up from the Item table, but the PO Line table does not update with this value.
Cant find what I am looking for on the forum so if anyone can help me or tell me what to search for that would be great...
I need one of my forms to display a running total of updated records, but only those records that the user clicks a command button on. i.e if i have a recordset of n records each time I move to the next one (by clicking a command button) I want the control on the form to display the count of those records that have been updated only.
I am assuming I would need to add some code to the after update event of the form or on click event of the command button but cant really figure out what code to write.
I have a form and I want show a message on the form when the it is locked as another user is editing the data in a particular record.
I know the record selectors show the records lock status but it a very tiny symbol which will mean nothing to the users of the database and anyway I don't want record selector bar on the form. How I would do this???
I have a question about a form which has several fields on it. What I need to do is to capture the ALL the fields of the record I just entered(updated or inserted)
So, for example, if my form has fields for
RepID, RepName, Company
After I edit or insert a new record, when I click to the next line, is there a way I can use AfterUpdate event to capture ALL the fields that were just updated on the form, and do whatever it is I need to do with them? I hope I am amking myself clear. Thanks for taking the time to look.
I have a form based on a mysql table. There is a button in the footer to add a new record.
The pertinent vba code: DoCmd.GoToRecord , , acNewRec
It adds the new record and properly places the cursor in the first field. Immediately after the first letter is typed, the error message "Field cannot be updated" pops up. I can click ok and the message goes away and I am able to continue filling in the fields. The same thing happens if I add the record by use of the record selectors.
I have a form in datasheet view and on that form I have a yes/no tick box is it possible that when the tick box is ticked it locks the line from further entry.
I have a form that is just a display/edit type form and I don't want a blank "new" report to show at the bottom when it is opened up. Is there any way to turn this off or disable it?