Modules & VBA :: Confirm Changes Using Before Update Based On Specific Value?

Dec 24, 2014

looking to have the following code run only when I have a specific value present in one of my combo boxes: e.g. cbo.status = "Approved"

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Provide the user with the option to save/undo
'changes made to the record in the form
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If
End Sub

View Replies


ADVERTISEMENT

Modules & VBA :: Msgbox To Confirm Changes To Specific Control

Oct 3, 2013

I have a database in which users have the ability to search by last name...nothing fancy there. However, the users continue to forget to click the Find Client button and instead just type what name they are looking for in the LASTNAME control thus changing the record that they are currently in!!!

I am looking for a way to have a msgbox pop up any time the LASTNAME control is changed. I found the code below but this works for the entire record and I don't want them to have to click Yes every time they make change.

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If
End Sub

View 4 Replies View Related

Confirm Update Message Box

Apr 2, 2005

I would like to have an update (requery) button on my form, that when clicked opens a message box that asks "ARE YOU SURE YOU WANT TO UPDATE?", if yes, a yes button is pushed, which requeries the record and closes the message box. If a no button is pushed, the message box is closed without requerying the record.

Can someone walk me through the process?

View 4 Replies View Related

Tables :: Update A Specific Record Based On Matching Primary Key ID?

May 22, 2014

My problem is that I am trying to update a field (called 'Sold' which is a yes/no checkbox column) for a specific record whenever an event is triggered. I have two forms (derived from two tables), one is called frmInventory and the other is called frmSales. In frmSales, I made a combo box called 'cboItemID' that allows the user to select from a list of items from my inventory table. Each selection from the list has 4 properties, the first of which is the 'Item ID' from the inventory table. Lastly, I have a field in both frmSales and frmInventory called 'Sold' as mentioned above. What I want to do is that whenever I check/uncheck the box in the 'Sold' field in frmSales, I want the 'Sold' field in frmInventory to check/uncheck as well, but only in the record with an 'Item ID' that matches the 'Item ID' from the combo box selection. In other words, I want to match the 'Sold' field in frmInventory with the 'Sold' field in frmSales, but for only the record that has the same 'Item ID' primary key as the one I picked from my selection in the combo box from frmSales.

how to reference another table and check whether or not it's 'Item ID' primary key is identical to the one I specified from the combo box, and then take action to update the 'Sold' field if the IDs match.

View 1 Replies View Related

Modules & VBA :: Displaying Object Based On Specific Record

Aug 11, 2014

I am having bit of confusion in trying to come up with a code that will assist in completing my database display (for a warehouse rental database).

My aim is to have a form display a layout of the site and overlay an image over a store if its occupied else leave the image off if otherwise.

I have everything in my table plus a checkbox field that states if vacancy is "yes' or "no"; say I have 60 stores to rent and they have specific names (unit = A09) and I already 'drew' the layout on a form (all 60 of them) with renaming the boxes for each unit (Name = A09).

How can I program a code that upon opening the form it will like

If 'Box Name on form' = 'unit name on table' AND 'Vacancy = No' then 'redbox.visible = yes'

View 11 Replies View Related

Modules & VBA :: CASE Statement - Display Specific Text In A Field Based On Value Of Another

Sep 22, 2014

I have a lengthy CASE statement in my database that displays specific text in a field based on the value of another. Simple stuff but for some reason it randomly will not work on certain values, and never the same one twice. Is there a commonly known cause for this? I have verified that the spelling and spacing etc. are correct in my code so that shouldn't be causing the problem.

View 2 Replies View Related

Modules & VBA :: Displaying Specific Fields Of Data Set Based On Combobox Choice

Sep 19, 2014

I have a Table with 57 fields. I would like to display this table in a form as a subform, but only certain fields depending on what selection is made in a combo box.

For instance, if the user selects "Missing Information" in the Combo Box, then the form will show a few standard fields such as ID, Market, Sales Manager, and then some specific ones such as date missing information requested and date missing information received.

If the user selects another option, again the standard fields will remain plus a few different ones.

I have done much searching on this and feel like I am so close but so far. I have looked into controlling the record source of the subform, columhidden =false and a multitude of others. All of which may or in fact probably do work in this situation but I can't seem to put it all together.

View 4 Replies View Related

Modules & VBA :: Before / After Delete Confirm Events

Oct 4, 2013

I'm using Access 2007..I have a table that contains transaction records from a bound form.When the user enters a certain type of transaction, I create a corresponding transaction in the after insert event.

Example
User record - tranId = 1, tranAmt = 10, tranType = DISB, tranAssocID = 0
My record - tranId = 2, tranAmt = 10, tranType = RECV, tranAssocID = 1

When the user deletes a record, I want to delete my corresponding record using the tranAssocID ( delete * from tran_tbl where tranAssocID = (the tranID of the user's record)

Problem:
1) I don't know how to save the tranID of the users record
2) I don't know how to reference the saved tranID in the delete statement.

I'm using the before delete confirm event to try to save the users tranID to no avail.I'm using the after delete confirm to delete my record in a case statement - acDeleteOK...I tried to save the value of the ID in the form (Me.tid) to a public long variable, a hidden field on the form, and a temporary variable to no avail.

View 8 Replies View Related

Modules & VBA :: Update Specific Record In Table Using Unbound Field On Form

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

Modules & VBA :: Form Data To Update Based On Combo Box

Sep 4, 2014

I have a form named CustomerForm.I have a query named CustomerQ. On my form I have a combo box named combo6.Combo 6 lists all of the company names from my customer table and includes the autokey field which is hidden.When I click on a value in my combo6 I want the values on my form to then be based on the value from combo6 whereas at the moment I select a value in combo6 and nothing happens other than combo6 now displays a different value. how to refresh/ repopulate the data based on combo6 without having to create more forms and queries.

View 14 Replies View Related

Modules & VBA :: Update Table Field Based On A Query

Nov 16, 2013

I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.

I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.

I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.

I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"

I'd attach my version ....

View 2 Replies View Related

Modules & VBA :: Update Recordset Based On User Input Box

Oct 13, 2014

I have a form in my front end database that is supposed to allow a user to search for a record based on account number and then make changes to the that record and for it to save in the back end database. I have the search function working where it populates different input boxes on the form with what is stored in the back end database but I cannot get the update function to work. I have tried to assign each input box with a variable and then run an Update SQL function to update each of the fields but the updates are not storing. Any example of a successful update statement that uses VBA variables in it or a way to update a specific record via a recordset type function?

View 8 Replies View Related

Modules & VBA :: Use Different Column Based On User Input To Update Data

Jun 29, 2015

I'm attempting to build an import module so that my users can take data from different walks of life and import it into my tables. I'm doing this by setting up a module and allowing the user to specify which column data will come from when importing data. So lets say I have a field that is CustomerID in my table and user one pulls data from 1 place and in his excel or csv file, customerid is in field 1. Another user does the same thing and its in field 2.

How can i make some form of a loop that when I'm attempting to update data, i pull the correct column?

strSQL = "SELECT * FROM tblImportTable"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With rst
Do While .EOF = False
If IsNull(DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")) Then

[code]....

View 6 Replies View Related

Modules & VBA :: Update A Table With Dates Based On User Input

Dec 30, 2014

I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need creating a loop so it knows to go back and add days to the other values.

Here is what I have:

Code:
Sub Update_Dates()
Dim rs As Recordset
Dim db As Database
Dim lmsg As String
Dim transactiondate As Date
Dim strsql As String

[Code] ....

View 6 Replies View Related

Modules & VBA :: Update Recordset Based On Max Process Instance From Another Table

Jun 7, 2013

I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.

Code:
Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
emplid = rs![Employee Number]

[Code] ....

View 5 Replies View Related

Modules & VBA :: Update Record Based On Related Records Completed

Dec 4, 2014

I have 2 tables, one is like a main table, containing all of the main data, such as a Job Number, Customer, Quantity, etc. I have a second, related, table that acts a breakdown of information. There may be several related records to one main record, it entirely depends on the nature of the job.

What I'd like to do is run a function that looks at a main record, checks if all the related records COMPLETED field is ticked and then tick a field in the main record. I only want it to do this for records where all of the related records are COMPLETED.

View 3 Replies View Related

Modules & VBA :: Update Table Based On List Box Multi Selected Records

Nov 24, 2014

I have database with an userform called AssignWP, combobox called WPDevBy, listbox called List352 (Multi select) and table called Justified.I am trying to update one field WPDevelopedBy of the table as combobox value based on list box multi selected records.

View 2 Replies View Related

Modules & VBA :: Search CSV To Find Specific Statement / Text On Specific Line?

Jul 8, 2015

how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.

If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).

The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).

View 3 Replies View Related

Modules & VBA :: Update Column In Table Based On Another Table

Apr 24, 2015

I'm trying to use VBA to update a new column in a table with info I already have in another table.The table I want to update is an inventory details table, it has around 25,000 records. I added a column called "UnitCost", of course the column is empty for all 25,000 records so I would like to fill it easily using DoCmd.RunSQL "UPDATE" feature.

I use that through-out the program however I'm unable to connect the dots for this one.What it needs to do is update "UnitCost" in "InventoryDetails" from "Products" where "InventoryDetails.ProductNumber" = "Products.ProductNumber"

The "Products" table has all the different unit cost, it just need to be placed in the "InventoryDetails" table for every record. Of course product1 needs products1 unit cost and product2 needs products2 unit cost, etc.

View 1 Replies View Related

Modules & VBA :: Update Field Value Based On Another Field

May 11, 2015

I have the following fields in an A2010 form

PaymentMethod
Total
InstalmentAmounts
OurFee

InstalmentAmounts field is populated based on the results of PaymentMethod which is a combo box

Here is the current code

Select Case Me.Payment_Method.Value
Case "Three post dated Cheques"
'If three payments by cheque
Me.InstalmentAmounts.Value = Round(Me.Total.Value / 3, 2)

In some cases there will be a few pennies that the client "overpays" because the instalment amounts will not add up to the Total. I need to add up the TotalInstalmentPayments to find out what the client actually pays and then add the difference between TotalInstalmentPayments and Total to the OurFee field

So

Total = 500.00
Payment Method = 3 instalments so
IstalmentAmounts = 166.67 (rounded) so client pays 500.01 so the 0.01 needs to add to the OurFee field.

View 4 Replies View Related

Update Specific Record From Excel

Jan 18, 2015

I'm trying to find best solution for my small "kanban apliccation" on shopfloor

On PC's I have excel form which adding (INSERT function)data to one access database.DB looks like:

ID Stockcode Qt Date
1. Apple 5 2015-01-18 22:35:01
2. Orange 3 2015-01-18 22:36:03
3. Apple 2 2015-01-18 22:37:34
4. Orange 6 2015-01-18 22:39:56
5. Apple 7 2015-01-18 22:41:59

Every row means stockcode and last quantity. How to rebuild function in excel or make a quere or use access vba to have just one stockcode with LAST qt? Qt should be updated somehow based on specific stockcode

View 1 Replies View Related

Criteria Based On Date Results In Specific Value

Dec 14, 2013

I don't know what it would be called or even how to start doing it in access other than it requires a criteria here is what I'm trying to make happen with access

name date yes/no criteria would be set to date > 180 days then it would equal value of No < 180 days then equal value Yes

If i set a today's date value on the database and then criteria is based of the value in the date box with the above information how would this be done.

View 4 Replies View Related

Print Specific Report Based On Combo Selection?

Apr 18, 2006

Hi

I need to print a specific form based on the selection made on a combo box.

I have been able to set it up to open the relevant form when the user selects an option from the combo box, but am unable to find a way to be able to print the specific report based on the selection.

I have a command button which prints a report based on the data in my current form, but would like it to also print the specific form based on the combo box selection.

Is this possible, and if so how is it possible?

Thanks in advance,

me

View 5 Replies View Related

Forms :: Updating Subform Using VBA Based On Specific Selections

Mar 8, 2013

I have a Form named 'Opening_Screen' which consists of various objects - Combo Boxes, RadioButtons, Check Boxes which the user can select.There is a subform called 'Report' within the 'Opening_Screen'. The source object of the subform is a query (called 'QueryX').

There is a button called 'Generate Report' on the form which when clicked - an SQL must run on the backend, update the query which in turn should update the Subform 'Report'.For me, the QueryX gets updated, but the subform doesn't. When I manually switch the form to Design View and then back to Form View - the subform gets updated. I tried the same through the vba code instead, but it doesn't update the subform.

View 10 Replies View Related

Queries :: Specific Date Range Based Off Of Other Data

Jun 14, 2013

I have multiple buildings that I own. Each building earns a monetary amount each day. Some days they earn $0, some days they earn $1,000. This is all kept track in a data base in which someone manually enters the information each day.The three fields are:

Building
Date
Profit

I have narrowed a query down to one building, and I am interested in two things. How do I write a query that shows a 30 day peak (the most profitable 30 days). In other words. I want to see the following
Building: GNB Tower Date: 02/14/13 - 03/15/13 Profit: $25,162

The next thing I am interested in is a 7 day peak from within these 30 days. This shouldn't be too difficult if I can figure out how to query these 30 days, I can query 7 days out of those 30 days. So this bigger mind stumper is the first one.

out of a date range of months and months (Let's say 10/12/13 - 6-14-13).Once I figure this part out, I am sure I can figure out how to compile all buildings into one large report with their 7 day peak and 30 day peak.

View 1 Replies View Related

Open Form And Go To Specific Record Based On Variable

Jun 9, 2010

I have two tables and two separate forms. [ID] is the link between them. (I cannot do a subform in this case).

Form1 displays 3 records

Have control button that goes to Form2 where [id] = NumID (NumID is "long" variable)

Have control button on Form2 to go back to Form1. It needs to go to the same record it came from. I don't want use a where clause (e.g., where [id] = [id]). This works, but the form1 is now filtered. I need all records available in Form1, but I want to go to the record it was on when the button was clicked to go to form1

Example:
Form1, record1, ID=30
Form1,record2,ID = 35
Form1,record3,ID = 40

Select record3,ID=40, then click on button to go to form2 (has where clause where ID=40).
Form2 is filtered and is correct.

Click on button to go back to form1. No Filter. It goes to 1st record (record1,ID=30). I need all records displayed, but need to be on record2,ID=35.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved