|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#21
March 3rd, 2005, 12:59 PM
|
|||
|
|||
Nikos,
The following is my code and the date format in my listbox is dd/mm/yyyy, same with all other dates in my db. the code includes the original that you sent me. thanks again Nikos Dim strSQL As String Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Right(Date1, 2)) Date2 = DateSerial(vYear, vMonth, vDay) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) DoCmd.RunSQL strSQL Date1 = Me.lsbxAvailableEmployees.Column(1) Date2 = DateSerial(Year(Date1), Month(Date1), Day(Date1)) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Date2 & "#" DoCmd.RunSQL strSQL "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(OrderI D) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Emplo yeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Start Date) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Durat ion) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this ! 
|
|
#22
March 3rd, 2005, 01:03 PM
|
|||
|
|||
|
Nikos,
I have also tried this, i changed the date parameters is this correct, is it changed to dd/mm/yyyy Date1 = Me.lsbxAvailableEmployees.Column(1) vDay = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vYear = Val(Right(Date1, 4)) Date2 = DateSerial(vDay, vMonth, vYear) "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(OrderI D) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Emplo yeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Start Date) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Durat ion) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
|
#23
March 3rd, 2005, 01:05 PM
|
|||
|
|||
|
Nikos,
sorry Nikos, i did the following and i am still having the same problem I have also tried this, i changed the date parameters is this correct, is it changed to dd/mm/yyyy Date1 = Me.lsbxAvailableEmployees.Column(1) vDay = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vYear = Val(Right(Date1, 4)) Date2 = DateSerial(vDay, vMonth, vYear) "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(OrderI D) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Emplo yeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Start Date) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Durat ion) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
|
#24
March 3rd, 2005, 01:08 PM
|
|||
|
|||
|
Don,
If your date format is dd/mm/yyyy then 05/01/06 was a very bad example! In light of this, change the date manipulation code to: Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Left(Date1, 2)) Date2 = DateSerial(vYear, vMonth, vDay) Nikos DonMoody wrote: Nikos, The following is my code and the date format in my listbox is dd/mm/yyyy, same with all other dates in my db. the code includes the original that you sent me. thanks again Nikos Dim strSQL As String Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Right(Date1, 2)) Date2 = DateSerial(vYear, vMonth, vDay) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) DoCmd.RunSQL strSQL Date1 = Me.lsbxAvailableEmployees.Column(1) Date2 = DateSerial(Year(Date1), Month(Date1), Day(Date1)) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Date2 & "#" DoCmd.RunSQL strSQL "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(Orde rID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Emp loyeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Sta rtDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Dur ation) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0 ) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1 ) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column( 0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column( 0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
|
#25
March 3rd, 2005, 01:21 PM
|
|||
|
|||
|
Really, Really Sorry Nikos, i just got mixed up becuase of how it was being
displayed on the allocation table, i have put the following code in, it is still doing the same. Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vDay = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) "Nikos Yannacopoulos" wrote: Don, If your date format is dd/mm/yyyy then 05/01/06 was a very bad example! In light of this, change the date manipulation code to: Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Left(Date1, 2)) Date2 = DateSerial(vYear, vMonth, vDay) Nikos DonMoody wrote: Nikos, The following is my code and the date format in my listbox is dd/mm/yyyy, same with all other dates in my db. the code includes the original that you sent me. thanks again Nikos Dim strSQL As String Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Right(Date1, 2)) Date2 = DateSerial(vYear, vMonth, vDay) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) DoCmd.RunSQL strSQL Date1 = Me.lsbxAvailableEmployees.Column(1) Date2 = DateSerial(Year(Date1), Month(Date1), Day(Date1)) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Date2 & "#" DoCmd.RunSQL strSQL "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(Orde rID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Emp loyeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Sta rtDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Dur ation) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0 ) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1 ) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column( 0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column( 0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
|
#26
March 3rd, 2005, 02:04 PM
|
|||
|
|||
|
You are confusing me. Try to work out how Left, Right and Mid work, and
adjust your code to what the lsitbox returns (check out Access help on the syntax, if in doubt). Also check out th syntax for DateSerial, the arguments are ALWAYS year, month, day, so don't change that! DonMoody wrote: Nikos, sorry Nikos, i did the following and i am still having the same problem I have also tried this, i changed the date parameters is this correct, is it changed to dd/mm/yyyy Date1 = Me.lsbxAvailableEmployees.Column(1) vDay = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vYear = Val(Right(Date1, 4)) Date2 = DateSerial(vDay, vMonth, vYear) "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(Orde rID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Emp loyeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Sta rtDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Dur ation) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0 ) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1 ) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column( 0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column( 0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
|
#27
March 3rd, 2005, 02:43 PM
|
|||
|
|||
|
Sorry Nikos,
i sent a couple of message at the same time. i have added to following and the syntex is correct, year, month and day Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Left(Date1, 2)) this is still appending the allocation table with the incorrect date and not dleting the availability table. "Nikos Yannacopoulos" wrote: You are confusing me. Try to work out how Left, Right and Mid work, and adjust your code to what the lsitbox returns (check out Access help on the syntax, if in doubt). Also check out th syntax for DateSerial, the arguments are ALWAYS year, month, day, so don't change that! DonMoody wrote: Nikos, sorry Nikos, i did the following and i am still having the same problem I have also tried this, i changed the date parameters is this correct, is it changed to dd/mm/yyyy Date1 = Me.lsbxAvailableEmployees.Column(1) vDay = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vYear = Val(Right(Date1, 4)) Date2 = DateSerial(vDay, vMonth, vYear) "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(Orde rID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Emp loyeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Sta rtDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Dur ation) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0 ) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1 ) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column( 0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column( 0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
|
#28
March 3rd, 2005, 03:01 PM
|
|||
|
|||
|
What is the value assigned to Date2?
DonMoody wrote: Sorry Nikos, i sent a couple of message at the same time. i have added to following and the syntex is correct, year, month and day Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Left(Date1, 2)) this is still appending the allocation table with the incorrect date and not dleting the availability table. "Nikos Yannacopoulos" wrote: You are confusing me. Try to work out how Left, Right and Mid work, and adjust your code to what the lsitbox returns (check out Access help on the syntax, if in doubt). Also check out th syntax for DateSerial, the arguments are ALWAYS year, month, day, so don't change that! DonMoody wrote: Nikos, sorry Nikos, i did the following and i am still having the same problem I have also tried this, i changed the date parameters is this correct, is it changed to dd/mm/yyyy Date1 = Me.lsbxAvailableEmployees.Column(1) vDay = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vYear = Val(Right(Date1, 4)) Date2 = DateSerial(vDay, vMonth, vYear) "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(Or derID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(E mployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(S tartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(D uration) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0 ) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1 ) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column( 0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column( 0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
|
#29
March 3rd, 2005, 03:17 PM
|
|||
|
|||
|
Nikos, these are the following parameters that i have got :
thanks for the reply, sorry, i must be doing something stupid Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Left(Date1, 2)) Date2 = DateSerial(vYear, vMonth, vDay) I have not changed the date2 expression "Nikos Yannacopoulos" wrote: What is the value assigned to Date2? DonMoody wrote: Sorry Nikos, i sent a couple of message at the same time. i have added to following and the syntex is correct, year, month and day Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Left(Date1, 2)) this is still appending the allocation table with the incorrect date and not dleting the availability table. "Nikos Yannacopoulos" wrote: You are confusing me. Try to work out how Left, Right and Mid work, and adjust your code to what the lsitbox returns (check out Access help on the syntax, if in doubt). Also check out th syntax for DateSerial, the arguments are ALWAYS year, month, day, so don't change that! DonMoody wrote: Nikos, sorry Nikos, i did the following and i am still having the same problem I have also tried this, i changed the date parameters is this correct, is it changed to dd/mm/yyyy Date1 = Me.lsbxAvailableEmployees.Column(1) vDay = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vYear = Val(Right(Date1, 4)) Date2 = DateSerial(vDay, vMonth, vYear) "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(Or derID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(E mployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(S tartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(D uration) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0 ) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1 ) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column( 0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column( 0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
|
#30
March 3rd, 2005, 06:59 PM
|
|||
|
|||
|
Nikos, these are the following parameters that i have got :
thanks for the reply, sorry, i must be doing something stupid Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Left(Date1, 2)) Date2 = DateSerial(vYear, vMonth, vDay) I have not changed the date2 expression "Nikos Yannacopoulos" wrote: What is the value assigned to Date2? DonMoody wrote: Sorry Nikos, i sent a couple of message at the same time. i have added to following and the syntex is correct, year, month and day Date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Right(Date1, 4)) vMonth = Val(Mid(Date1, 4, 2)) vDay = Val(Left(Date1, 2)) this is still appending the allocation table with the incorrect date and not dleting the availability table. "Nikos Yannacopoulos" wrote: You are confusing me. Try to work out how Left, Right and Mid work, and adjust your code to what the lsitbox returns (check out Access help on the syntax, if in doubt). Also check out th syntax for DateSerial, the arguments are ALWAYS year, month, day, so don't change that! DonMoody wrote: Nikos, sorry Nikos, i did the following and i am still having the same problem I have also tried this, i changed the date parameters is this correct, is it changed to dd/mm/yyyy Date1 = Me.lsbxAvailableEmployees.Column(1) vDay = Val(Left(Date1, 2)) vMonth = Val(Mid(Date1, 4, 2)) vYear = Val(Right(Date1, 4)) Date2 = DateSerial(vDay, vMonth, vYear) "Nikos Yannacopoulos" wrote: Is your date format (as displayed in the listbox) indeed yy/mm/dd as I assumed, or is it different? If different, we need to make some changes in the handling. At any rate, run the code line by line and check the value of the variables at every step, to find the one that isn't what you expected. This always helps debugging. If still in doubt, post your exact code so I can have a look. Nikos DonMoody wrote: Nikos, sorry Nikos, but this is causing the same problem again but this time the number changed from 00:03:35 to 00:04:17, i cant understand why. is there any other way. "Nikos Yannacopoulos" wrote: Don, The format of the table field or form control affects only how the data is displayed, not how it is stored. Now, in your case: i did a test and found that the catch here is that the date, read from a listbox column, is actually returned as a text string rather than a date! This taken into account, you'll just need to play around with a few functions to make it work. Assuming 05/01/06 in your example means Jan.6, 2005 (unfortunate choice of example, could be anything!), so, date format is yy/mm/dd, the first two lines (date1 = ...., date2 = ...) should be replaced with: date1 = Me.lsbxAvailableEmployees.Column(1) vYear = Val(Left(date1,2)) vMonth = Val(Mid(date1,4,2)) vDay = Val(Right(date1,2)) date2 = DateSerial(vYear, vMonth, vDay) I believe this should do the trick. Nikos DonMoody wrote: Hi Nikos, Thanks the query is still behaving the same, it appends with the incorrect date and does not delete the record. the date is displayed as follow, 05/01/06 is displayed 00:03:35 i have checked the format for both of these field and they seem to be the same. if i only stored the Employee Id on the lisbox, could it be posible to append all the information for that employee id to the allocation table and delete the information for that employee id from the employeeavailblity table. just an idea thanks again Nikos. "Nikos Yannacopoulos" wrote: I bet your date format in regional settings is not US, right? the problem is, VBA only understands US format, and assumes any date to be US formst, regardless of your regional settings... see if this solves it: date1 = Me.lsbxAvailableEmployees.Column(1) date2 = date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & date2 strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError date1 = Me.lsbxAvailableEmployees.Column(1) date2 = DateSerial(Year(date1),Month(date1),Day(date1) strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & date2 & "#" DoCmd.RunSQL strSQL (watch out for wrapping) Nikos Nikos Yannacopoulos wrote: Try enclosing the date control reference in hashes: strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = #" & Me.lsbxAvailableEmployees.Column(1) & "#" CurrentDb.Execute strSQL, dbFailOnError As always, watch out for wrapping. Nikos DonMoody wrote: Nikos, Thanks once again, you are right, this query works partly. the date field is not being appended correctly and and the delete is not working. the date formats seem to be the same. "Nikos Yannacopoulos" wrote: Don, The error message is because you are using the "names of fields" as index in the Column() property, which is not correct; the index argument is numeric, columns don't have names. Switching back to the numeric indices as I proposed, the "nothing happening" is most likely good news! It means the queries are run without errors - that is, a record is definitely added to tblAllocation, and a record is probably deleted from tblEmployeeAvailability (if one satisfies the criteria). One of the good things about CurrentDb.Execute (as opposed to DoCmd.RunSQL) is that it does not ask for confirmation, which you would normallyu not want in this case. Therefore, the only way to see the results of the code in this case is to check the tables after you click the button! If you want, you can temporarily change the CurrentDb.Execute with DoCmd.RunSQl strSQL so you get the warnigns and know what is going on. Nikos DonMoody wrote: Nikos, first i would like to apologise for any mistakes that i have made or silly questions i have asked and i do take on board what you say and really apreciate your help. i am learning all the time. i have entered this query into my db and change the expression, first nothing was happening when i clicked the button but when i changed the column arguments from numbers to fieldnames, the following error came up "run time error '3134' syntax error in INSERT INTO statement" this is what i have in there at the moment Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration) CurrentDb.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1) CurrentDb.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Separation of the values in the SELECT clause with commas is done through the & " , " & part preceding each from the second one on. The error is probably my fault, which I just noticed, and for which I apologise. The correct expression is: CurrentDb.Execute strSQL, dbFailOnError The procedure where I copied it from (lazy me!) had a Set db = CurrentDb statement at the beginning, so it worked fine there, but not in your case without it. By the way, when you post with an error, please do take the trouble to include the error description, nobody remembers all those error numbers. Nikos DonMoody wrote: Nikos, sorry for asking stupid questions, i have changed the column arguments back to your example but still coming up with the same error. sorry to sound stupid what do you mean separting the select clause with commas, how do i do this? sorry and thanks for the replies "Nikos Yannacopoulos" wrote: Because of the changes you made in the column argument. I had the numbers there for a reason, and I expalined how it works! DonMoody wrote: I have inserted the following but it is still comin back with error 424, i cant understand why, it highlights "db.Execute strSQL, dbFailOnError" in yellow, i cant understand why Dim strSQL As String strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders.Column(Or derID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(E mployeeID) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(S tartDate) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(D uration) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column(0 ) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column(1 ) db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, You need to reference the three columns in the listbox explicitly, like Me.lsbxAvailableEmployees.Column(x), the first column being 0, the second being 1 etc. Also, you need to separate the values in the SELECT clause with commas, without repeating the field names, which you have already declared in the INSERT clause, so it works to a one-to-one assignment (look at my original). Modify your code as follows: strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(0) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(1) strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(2) db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees.Column( 0) strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees.Column( 0) db.Execute strSQL, dbFailOnError Look out for text wrapping in your newsreader! HTH, Nikos DonMoody wrote: Hi Nikos, thanks for the reply i have inserted the following VBA into the click event of a command button but it does not seem to work, is there something i am doing wrong, i have three fields in the list box, does make a difference ? strSQL = "INSERT INTO tblAllocation ( OrderID, EmployeeID, Startdate, Duration )" strSQL = strSQL & " SELECT " & Me.cmbxOutstandingOrders strSQL = strSQL & " EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " Startdate = " & Me.lsbxAvailableEmployees strSQL = strSQL & "Duration = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lsbxAvailableEmployees strSQL = strSQL & " AND StartDate = " & Me.lsbxAvailableEmployees db.Execute strSQL, dbFailOnError "Nikos Yannacopoulos" wrote: Don, Use an append and a delete query in code, something like: strSQL = "INSERT INTO tblAssignment ( OrderID, EmployeeID )" strSQL = strSQL & " SELECT " & Me.cboOrder strSQL = strSQL & " , " & Me.lstEmployee db.Execute strSQL, dbFailOnError strSQL = "DELETE * FROM tblEmployeeAvailability" strSQL = strSQL & " WHERE EmployeeID = " & Me.lstEmployee strSQL = strSQL & " AND StartDate = " Me.??? db.Execute strSQL, dbFailOnError Or something like that. Note that if your date format (in Windows regional settings) is not US, you will need to account for that in your code, as VBA only works with US format. HTH, Nikos DonMoody wrote: Hello, i have selected the criteria on a form using 1 combobox and one llistbox, how can i update a table with this information using a command button. i have chosen a outstanding order from a combobox then i got the option of all available employees in a list box, of which i can select one, all i need now is to put this informatio into the allocation table and delete it from one table. can i do this !
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Insert, Update, Open to record LastModified | Liz James | General Discussion | 3 | February 1st, 2005 02:31 PM |
| Insert File as Link Update Question | susanp | General Discussion | 1 | August 10th, 2004 08:11 PM |
| after insert and after update events (confusion) | GeorgeB | Using Forms | 2 | June 18th, 2004 09:12 PM |
Linear Mode
