Insert and Update

[DonMoody]

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 !

 

[Nikos Yannacopoulos]

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]

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]

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]

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(OrderID)
strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(EmployeeID)
strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(StartDate)
strSQL = strSQL & " , " & Me.lsbxAvailableEmployees.Column(Duration)
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]

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]

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]

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]

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]

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]

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]

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]

Nikos,

thanks for the reply

this is still doing the same, the date field that is entered into the
allocation table is not correct, it looks like a number and the query does
not delete any records.

so when i click the button, it comes back with a message, saying that it
will append one record (because i have used the docmd.runsql command) and it
inserts the correct employee id, the correct order id and durationbut the
incorrect startdate

and then another message after i click yes saying that it will delete no
records.


this is the same after i entered the new script.

thanks

 

[John Vinson]

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 !

I really don't understand what you're asking. It seems you're mixing a
lot of things - a parameter query, a delete query, an append query...?

Please explain the nature and relationships of your tables, and what
you're trying to accomplish with reference to the tables.

John W. Vinson[MVP]

 

[DonMoody]

Hi John,

I am trying to append and delete a record at the same time.

i have 2 queries running together :-

query 1 - retirievs all outstanding orders - uses Order table - this is a
combobox
query 2 - this is two queries joined together using a range of tables, it
retrieves the employee which has the expertise and is available for the order
- this is a listbox

there are also two tables on the form,

the first table is the order table, which displays all order details from
the selection of the outstanding orders.

the second table is the employee table which display employee details after
the selection of the suitable employee.

i have a button, which at the moment has the coding Nikos (See Above) has
provided for me.

i need the button, once clicked, to add the orderID from the outstanding
orders combobox,
EmployeeID, StartDate and Duration from the listbox to a table called
allocation but at the same time delete the employee availablity from the
employee availablity table. also it would help to change status of the order
id in the order table to in progress.

 

[Nikos Yannacopoulos]

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

 

[DonMoody]

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]

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]

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]

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