SQL Insert Into Fails - Update Fields That Do Have Data Entered
Dec 14, 2012
The following SQL event will not update the CustomerData table if the FrmContact field on the form is left blank. I have this code in the OnLostFocus Event. Is it possible to have this command update the fields that do have data entered in them even though the FrmContact field is blank?
DoCmd.RunSQL "INSERT INTO CustomerData (CustomerName, CustomerAddress, CustomerCityStateZip, CustomerPhone, CustomerContact, LockRecs) " & " VALUES (""" & Forms!frmflcdeliver.Controls!FrmCompany & """ , """ & Forms!frmflcdeliver.Controls!FrmAddress & """, """ & Forms!frmflcdeliver.Controls!FrmCityStateZip & """, """ & Forms!frmflcdeliver.Controls!FrmPhone & """, """ & Forms!frmflcdeliver.Controls!FrmContact & """, """ & Forms!frmflcdeliver.Controls!FrmLocked & """)"
View Replies
ADVERTISEMENT
Mar 3, 2013
I upgraded my system to Windows 8 and Office 2013, and it's now 64 bit instead of 32 bit as before. Also note that I'm still using Access 2010 as before.So the following problem now arise in this new config that was not there before.
When you do a calculation like '332.16 - 1', the answer is now 331,16, instead of 331.16. Note the point are now replaces with a comma.
Might sound trivial, but everywhere in my code where is have a dynamic SQL like:
Currentdb.EXECUTE "INSERT INTO Tasks (TaskID, MyNumber)
VALUES ("Tsk123", Var1 - Var2)",
the result was always something like:
INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331.16), but now it is:
INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331,16), so it is seen as an extra value and the statement fails.
So, yes I can fix this one statement, but I have multiple occurrences of this throughout my app. How do I fix this?
And changing something in my Control Panel - Regional Settings would not be the answer, as on all the client PC's it might be different.
View 3 Replies
View Related
Jun 11, 2006
I have a form that student grades on. I use a listbox to pick the names. I also have a pop up form for me to enter new students that are not in the pop up "query" form. I can go to my form with the grades and hit a refresh button and the listbox is updated. I would like for my data to be requeried and up to date when I close the pop form. Any suggestion.
Thanks
View 3 Replies
View Related
Sep 9, 2005
Hi, i am trying to perform a beforeupdate event on a record, but seem to be getting an error.
The undo function works fine but it cannot save, i would be grateful if someone can help as the error is unclear.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Are you sure you want save these changes?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Else 'user clicked no
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If
End Sub
Error that i am getting.....
Run-time error '2115'
The Macro or function set to the beforeUpdate or ValidatationRule propety fot this field is preventing from saving the data in the field.
Thanks
View 1 Replies
View Related
Apr 19, 2005
I have a form which has a table as it's Record Source. The problem lies when a bound combobox or bound textbox is changed, the change is noted on the form but when I reference the value of the object it comes back as null. So I look in the table and the field has not changed. But if I requery the form then it takes.
I've never had this happen to me before, is there something I am missing? :confused:
View 3 Replies
View Related
Sep 9, 2013
How to disable fields in Access based on data entered in a previous field? For example if "yes" is chosen from a drop down show "Date field" if "no" is chose hide "Date Field".
View 2 Replies
View Related
Jul 30, 2012
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day)
- contains 'name' 'date' and '# of hours' worked
- an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month)
- contains the number of contracts worked that is derived from seperate system
- this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January)
- the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours)
- employees dont have access to this system to enter their own # of contracts keyed on a daily basis.
- for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
View 2 Replies
View Related
Oct 21, 2005
Gurus,
I seriously need some help with the following.
What I need to do is when I enter the data in the form and before I go to the next record I want to save this row in the table and then insert two more rows with the same values (that I just entered and saved in the table) while incrementing the index field. I also want to programatically manipulate a field value in the second and third rows that I will insert before commiting.
For Example:
Tbl-A
Reg_Num Number (Index)
Name Text (30)
Reg_Type Text (1)
Category Text (10)
Sub_Category Text (10)
Short_Desc Text (10)
Long_Desc Text (50)
1st row:
10001, "PSUS", "A","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"
After I enter these values I click on "SAVE" button. When I click on save, I want to insert the same row twice (notice Reg_Num and Reg_Type values) in the table and increment Reg_Num and change the value of Reg_Type.
2nd row:
40001, "PSUS", "W","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"
3rd row:
60001, "PSUS", "X","CREDIT CARD", "FEE", "CCF", "CREDIT CARD FEE"
As you see, I am incrementing the index field value by 30,000 if Reg_Type is "W" and by 50,000 if the Reg_Type is "X". What it means is copy the row that I just entered, insert it in the table, change the Reg_Type value and increment Reg_Num based on the business rule. COMMIT. Do the same for the next row.
I have about 20 Fields in the table. I do not want the user to type 10 times the same values and make mistakes. This is just an example. I have about 8 different Reg_Types that needs to be stored in the table everytime a new Reg_Num is entered with Type "A". User can enter any Reg_Type but to make my life easier I'm restricting to start from Reg_Type "A". For each Reg_Type the Reg_Num needs to be computed because it is extreamly important in our application.
Hope I explained it right.
Thanks a million in advance.
PS
View 3 Replies
View Related
Jan 9, 2015
I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form
Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.
I know I am duplicating the data by having customer name in both tables which is bad database design!
View 3 Replies
View Related
Apr 10, 2008
Access2007- Building a query to handle future input...
I run an update query that does what it is supposed to do and gives the proper values.
THEN I add a one more field to the source with the builder using + that has no data in it yet, the query fails and gives blanks, even though one of the fields in the source has data in it.
I would think that the "no data" field would be considered 0.00 value and be added together to the field that has values giving at least the value of the field with values as a result of the query.
Is there a setting somewhere that I need to change so that the no value and the value would be combined? I am using the builder to combine the values in the query.
Thanks.
View 2 Replies
View Related
Oct 22, 2004
I am an Ms Access newbie and need some help on how to update 2 fields from a drop down list.
The drop down list has 5 columns. The first column is the partname that is bound and updates the table with that partname. I want to take the
listprice which is the 5th column and update a field in the table with the listprice that corresponds to the part name.
Thank you.
View 5 Replies
View Related
May 8, 2013
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
View 1 Replies
View Related
Jan 8, 2015
I'm a maintenance coordinator for a small trucking company and I'm putting together an access database to track and manage all of our fleet maintenance. I'm rank amateur when it comes to access but I've been doing ok with it and so far I've been able to get it to do almost everything I set out to have it do.
But I'm having a hard time figuring out how to make this trick work. Here's what I want it to do. We have two groups of trailers, working trailers and storage unit trailers. We do preventative maintenance every six months on road trailers and once a year on storage units. I have a field in tblEquipmentMaster which shows the last PM date. I have a query and an report which use that LastPMDate field as well as the value of a StorageUnit checkbox to calculate what units are due/overdue for PM.
In addition to all this, I've got tables and forms set up to track maintenance records. I'm tracking the maintenance invoices as well as the details of each line item on the invoices (changed water pump, replaced tail light, PM service etc). So what I'd like to have happen is for the LastPMDate field in tblEquipmentMaster to update to the value in InvoiceDate in tblMaintenance anytime the field Description in tblMaintenanceDetails contains the text 'PM' for the unit number in question. Alternatively tblMaintenanceDetails also has a lookup field which contains maintenance codes. So the field VMRSCode could also be used as the trigger anytime the value 'PM' is selected.
View 3 Replies
View Related
Nov 1, 2012
I'm running an update query that's based on a select query (that runs some calculations). The update query is updating ALL the rows that should be updated with the information in the select query with data from the final row in the select query, and not on a per ID basis as I think I have it set-up to do. The data looks correct in the update query, but again it's not coming out right.
Here's the SQL for what I've written so far:
UPDATE [Customer_Data Query], Customer_Data INNER JOIN Baseline ON Customer_Data.ID = Baseline.ID SET Baseline.[Unit Hours] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units], Baseline.Availability = [Customer_Data]![Perceived_Avail], Baseline.[Hours Available] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units]*[Customer_Data]![Perceived_Avail],
[Code] ....
I've also tried to force the update to the proper row by adding a criteria based on ID.
Select query, here it is:
SELECT Customer_Data.ID, Customer_Data.Data_Set_Version, Customer_Data.Number_Units, Customer_Data.Perceived_Avail, Customer_Data.MTTR_MTBF, Customer_Data.MT_TR_OR_BF_Hours, Customer_Data.Utilization, Customer_Data.Percent_Scheduled, Customer_Data.Sched_Percent_of_PM, Customer_Data.Sched_PM_Duration, Customer_Data.Sched_CBM_Duration, Customer_Data.Sched_CBM_From_PM, Customer_Data.React_Detect, Customer_Data.React_Rework, Customer_Data.React_False_Alarms,
[Code] ....
View 3 Replies
View Related
Sep 17, 2013
I have a log in screen called frmLogIn and on there i have Unbound Combo Box cboUser and Unbound Text Box txtPWord. I also have cmdOK.
IF the user sucesfully enters his password it takes him to frmMainMenu.
On that form i have a lable called lblYouAreCurrentlyLoggedInAs and a unbound text box called txtUserName.
How do i update that txtUserName to whatever was entered in the previous form's cboUser.
View 1 Replies
View Related
Nov 7, 2013
I'm trying to update all the rows in a column (column A, PO Number) within a table (iSupplierTable). The value (txtPONbr) is entered by the user on a form (NewPO).
Code:
Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "iSupplierTable"
[code]....
View 9 Replies
View Related
Mar 15, 2007
I have created a query that will pull the price of a property into the cost field combo box on a booking order subform depending on the values of the start date and property number enter onto the same form.
The query gets the price from the property price table matching on the property no i've selected on the booking order subform and also the start date i've entered on the booking order subform which needs to be between the start and end date fields in the property price table.
Fields in the property price are property price no, property no, start date, end date and price.
Also the booking order subform is a subform on a booking form
This is the query i have created:
SELECT [Property Price].Price
FROM [Property Price]
WHERE (((forms![booking order subform]![start date]) Between [property price].[start date] And [property price].[end date]) And (([Property Price].[Property No])=forms![booking order subform]![property no]));
The problem is when i run the query from the cost combo box the query isn't picking up the fields on the booking order subform instead its bring up a seperate meesage box from parameters start date and property number to be enter.
Can someone give me advise how to make the query use the values in the fields on the booking order subform i enter before running the query. cheers
View 2 Replies
View Related
Nov 7, 2007
I have a database for police officers to enter their daily activity. For example on arrests, Date:_____ Drugs____DUI___
The officers enter the date and the total number arrested for each charge. I want to print a monthly report. How do I make the report print this information for a specific date range? The way I have it now, it asks for the startdate and enddate, but it still totals every record, not just the date range.
View 14 Replies
View Related
Dec 16, 2013
I have a form (based on ArtistSong) and a subform tied to the ArtistSongID. This subform is a set of 11 variable attributes of that Artist & Song combination. Also, there can be multiple records in that subform, all associated with the Artist & Song.The user would like, when he makes an initial entry of 11 attributes, to go ahead and repeat that particular combination in the next New Record.
View 3 Replies
View Related
Jul 2, 2010
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
View 2 Replies
View Related
Jul 26, 2014
I am trying to add the current date and time into separate fields after an ID is entered.
Code:
Option Compare Database
Private Sub ID_AfterUpdate()
Me.Date_Received = Date()
Me.Time_Received = Format(Now(), "hh:mm AMPM")
End Sub
View 5 Replies
View Related
Nov 27, 2007
Hello,
My problem is the pop up message that appears when I do the insert and update into my tables. It is annoying and i'd like to take it off...
Is there any way to take that off??
Thanks in advance.
View 4 Replies
View Related
Jan 13, 2005
Hi folks,
I need some help to figure this out..
What I am trying to do is, when I click the "Save" button on the form I also want to save some of the fields into another table.
I want to open the other table and search of the key field (command ?)
If found then
----run update sql statement
else
----run insert statement
end if
close the table
I am looking for the command statements... (i have the insert and update sql statements ready)
Thanks in advance..
binjos
View 11 Replies
View Related
Mar 6, 2013
how can i to save the values entered into unbound text boxes to fields in table
View 2 Replies
View Related
Mar 3, 2006
Hi All,
I've got a form with a series of combo boxes which are dependent on each other, however i keep finding that after ive entered some data and left a record, when i then return to the record, some of the information ive entered has been deleted and the combo box is blank.
I think it must be requerying the combo boxes everytime the record opens but i dont know why, i have only put requerys "OnChange" because that is the only time the dependent combo boxes should be requeried.
Once the info is entered and ive moved on to a new record, i need for it to stay that way when i return to the record and only change again when i edit it.
Help please anyone, its been doing my head in.
Cheers
K
View 4 Replies
View Related
Dec 5, 2005
Humm, been going over this and can't figure where I am going wrong.
Linked SQL server table.
SQL permissions are correct (cause every thing works via QA using pass through security).
Form with a CBO to select Item1, once selected list box populates with Items tied to it.
Select item from list box click button to break the tie between the two.
3 tables, Item1 Table, Item2 table and cross reference table that ties them together. The Break Tie basically (should) remove the entry from the cross ref. table. Form allows me to insert a new tie (same premis as break only allows you to select items not tied currently) and will insert a row into the cross ref. table. But it will not allow me to delete, or as a backup plan I tried to zero out the FK values. Delete says not allowed (bad permissions, table read only, etc.) and Update says must use an updatable query.
NOW the queries I have used are basically DELETE FROM CrossRef Where PK = nnnn OR UPDATE CrossRef SET FK1 = 0, FK2 = 0 WHERE PK = nnnn
I mean it does not get any easier than that.
So what am I missing on this? Why won't it let me delete/update that stupid table?
View 1 Replies
View Related
