Append and update query with vba

[mischa]

Hi,

I got two questions:

1.

I have written a vba code which should append data to a table called; 'tblActionsTaken". The data is given by the user. access works with 2 of the 3 fields. The third field (or first in de vba code) gives an error. The value is selected in a combobox containing a Yes or No, this value has to be transferred to the field called "LastTimeBuy" which Yes/No Boolean data type. I verified, by using a MsgBox that the value of the combobox is "True" or "False". The error I get can be found in a screenshot which I attached to this post.

The code I'm using is as follows:

DoCmd.RunSQL "INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) " & "VALUES(" & "'" & Me.lstLastTImeBuy & "'," & "'" & Me.txtActionDate & "'," & "'" & Me.txtActionDetails & "') "

2.

Subsequently I would like to use the added information (from question 1) and link this to a value in another table. Therefore I need the autonumber which Access has given to the row. How can I get this value and use it in an update query (which will be called in vba code)
​

I hope someone could help me with one or both of my questions.
Thanks in advance!

 

[CJ_London]

1. Your problem is due to you are trying to assign a text value (combo boxes are text) to a yes/no field. Try:

"INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) " & "VALUES(" & Me.lstLastTImeBuy="True" & ", .....


2.rather than using docmd.runsql do the the following:

Dim myDb as Database
Dim rst as Recordset

myDb=Currentdb

mydb.execute("INSERT INTO....etc
set rst = mydb.openrecordset("SELECT @@Identity as LastID")
LastID=rst.fields(0)

set rst=nothing
set mydb=nothing

 

[mischa]

Thank you very much CJ_London!

I will try this and get back to you if neccessary.

 

[mischa]

1. Your problem is due to you are trying to assign a text value (combo boxes are text) to a yes/no field. Try:

"INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) " & "VALUES(" & Me.lstLastTImeBuy="True" & ", .....


2.rather than using docmd.runsql do the the following:

Dim myDb as Database
Dim rst as Recordset

myDb=Currentdb

mydb.execute("INSERT INTO....etc
set rst = mydb.openrecordset("SELECT @@Identity as LastID")
LastID=rst.fields(0)

set rst=nothing
set mydb=nothing

CJ_London, I've implemented the code into the database as followed:
Note that I changed the value to Yes instead of True, because I changed the value of the combobox to "Yes";"No".

        Dim myDb As Database
        Dim rst As Recordset
        Set myDb = CurrentDb
        myDb.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) " & "VALUES(" & Me.lstLastTImeBuy = "Yes" & "," & "'" & Me.txtActionDetails & "') ")
        Set rst = myDb.OpenRecordset("SELECT @@Identity as LastID")
        LastID = rst.Fields(0)
        
        Set rst = Nothing
        Set myDb = Nothing

Access is giving an error which can be seen in the attachment to this post. It is referring to a table or query called "false" but I don't know why. The table name is correct which can't be it.

Is it correct that if I use the following code functionality 2 (as described in the first post) will work? Or should it be altered ? My concerns are regarding the LastID part in the INSERT sql code.

        LastID = rst.Fields(0)
        myDb.Execute ("UPDATE tblAltPart Set ltPN=Me.lstActionSelectPart.value, ActionID=LastID")        
        
        Set rst = Nothing
        Set myDb = Nothing

Thanks in advance!

 

[CJ_London]

myDb.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) " & "VALUES(" & Me.lstLastTImeBuy = "Yes" & "," & "'" & Me.txtActionDetails & "') ")

Looks like you are trying to insert 3 fields, but only providing 2. I would also get rid of the superflous double quotes to make it easier to read

myDb.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES(" & Me.lstLastTImeBuy = "Yes" & ", " & me.txtactiondate & ", '" & Me.txtActionDetails & "')")

 

[CJ_London]

sorry - missed this part of your post

myDb.Execute ("UPDATE tblAltPart Set ltPN=Me.lstActionSelectPart.value, ActionID=LastID")

should be

myDb.Execute ("UPDATE tblAltPart Set ltPN= " & stActionSelectPart & ", ActionID=" & LastID)

But you also need a where clause - otherwise which record is it going to update?[/QUOTE]should be

myDb.Execute ("UPDATE tblAltPart Set ltPN= " & stActionSelectPart & ", ActionID=" & LastID & " WHERE someid=" & me.someid)

 

[mischa]

Looks like you are trying to insert 3 fields, but only providing 2. I would also get rid of the superflous double quotes to make it easier to read

Thanks CJ_London!

While editing the code, I accidentally forgot to add the third field.
I also found two misspelled names.
This part of the code should be:

            myDb.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) " & "VALUES(" & Me.lstLastTImeBuy = "Yes" & "," & "'" & Me.txtActionDate & "'," & "'" & Me.txtActionDetails & "') ")

Unfortunately Ii still get the same error as in the attachment to my previous post (which refers to a table/qry called 'false' which I don't have).

The complete code is:

            Dim myDb As Database
            Dim rst As Recordset
            Set myDb = CurrentDb
            myDb.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) " & "VALUES(" & Me.lstLastTImeBuy = "Yes" & "," & "'" & Me.txtActionDate & "'," & "'" & Me.txtActionDetails & "') ")
            Set rst = myDb.OpenRecordset("SELECT  @@Identity as SelectedID")
            SelectedID = rst.Fields(0)
            myDb.Execute ("UPDATE tblAltPart Set ActionID=" & SelectedID & " WHERE AltPN= " & lstActionSelectPart & "")
            Set rst = Nothing
            Set myDb = Nothing

 

[CJ_London]

you haven't read my post correctly - review it and compare with what you have

 

[mischa]

you haven't read my post correctly - review it and compare with what you have

CJ, sorry if I don't understand it well but I've tried altering the code with the things you said. Without the yes/no field it seems to work correctly but with that part I continuously get the same error.

Doesn't work:

myDb.Execute ("INSERT INTO tblActionsTaken (LastTimeBuy, ActionDate, ActionDetails) VALUES(" & Me.lstLastTImeBuy = "Yes" & "," & Me.txtActionDate & ",'" & Me.txtActionDetails & "') ")

Does work:

myDb.Execute ("INSERT INTO tblActionsTaken (ActionDate, ActionDetails) VALUES(" & Me.txtActionDate & ",'" & Me.txtActionDetails & "') ")

The value of Me.lstLastTImeBuy will be "Yes" or "No", which is determined by the user which it will be.

I don't know what I am doing incorrect but something is.
I really hope you could help me solve it .

 

[CJ_London]

OK,

I can't see any problem with the code, just ran a test and it worked fine so perhaps the problem is with your combobox.

Can you confirm the following for the combobox

ControlSource
rowsource
rowsource type
bound column
columncount
columnwidths
name

 

[mischa]

Thanks for your time!

Can you confirm the following for the combobox

Yes, ofcourse

ControlSource=Nothing
rowsource="Yes";"No"
rowsource type=Value List
bound column=1
columncount=1
columnwidths=Nothing filled in
name=lstLastTImeBuy

 

[CJ_London]

Nothing wrong there! Running out of ideas

One other thought - is the combobox empty (i.e. null) when you are running the code? I can see from your screenshot it wasn't but there were other issues then. If it is (or can be empty then you need to use the nz function:

nz(Me.lstLastTImeBuy)

Only other thing I can think of is the form has become corrupted in some way. Suggest do a compact (which might work) and/or rebuild the form to remove the corruption.

Also try debugging by putting

Debug.Print Me.lstLastTImeBuy = "Yes"

just before your execute line and see what it says in the immediate window

Also, I have been working on the assumption that your field LastimeBuy in your tblActionsTaken table is boolean - I presume this is correct?

 

[mischa]

Nothing wrong there! Running out of ideas

One other thought - is the combobox empty (i.e. null) when you are running the code? I can see from your screenshot it wasn't but there were other issues then. If it is (or can be empty then you need to use the nz function:

nz(Me.lstLastTImeBuy)

Only other thing I can think of is the form has become corrupted in some way. Suggest do a compact (which might work) and/or rebuild the form to remove the corruption.

Also try debugging by putting

Debug.Print Me.lstLastTImeBuy = "Yes"

just before your execute line and see what it says in the immediate window

Also, I have been working on the assumption that your field LastimeBuy in your tblActionsTaken table is boolean - I presume this is correct?

One other thought - is the combobox empty (i.e. null) when you are running the code? I can see from your screenshot it wasn't but there were other issues then. If it is (or can be empty then you need to use the nz function:

nz(Me.lstLastTImeBuy)

The combobox isn't empty and I included a procedure to force the user to select a value:

    If lstLastTImeBuy = "" Then
        MsgBox "Please select a if Last Time Buy has been done"
        Exit Sub
    End If

In the immediate window the value "True" is showed after running the debug code.

I exported the form with some of the queries and tables to show you a stripped version of the file. The strange thing is that it says it cannot find table/query called false. This is also the case in the new document.
I also tried using another combobox which didn't work and compact and repair neither.

If you download the attachment, you can simulate the error when you follow the following steps:
1) Open frmActions
2) Put in Add Mode
3) Set "lstActionCategory" to Alternate parts
4) Select a part
5) Select a last time buy action
6) Enter a Date using the access calendar tool (or whatever it is called)
7) Enter Action Details
8) Press Add action

Thank you very much for your time so far CJ_London!!

 

[mischa]

Nothing wrong there! Running out of ideas
Also, I have been working on the assumption that your field LastimeBuy in your tblActionsTaken table is boolean - I presume this is correct?

tblActionsTaken is of datatype Yes/No. Which ofcourse is boolean. So your assumption is correct .

 

[CJ_London]

solved the first problem - you need to put brackets round lstLastTImeBuy = "Yes"

VALUES(" &[COLOR=red] (Me.lstLastTImeBuy = "Yes")[/COLOR] & "," ....

You will then fail on the second query because AltPN is text whereas you had treated it as numeric so you need to alter your code to

mydb.Execute ("UPDATE tblAltPart Set ActionID=" & SelectedID & " WHERE AltPN=[SIZE=4][COLOR=red] '[/COLOR][/SIZE]" & Me.lstActionSelectPart & "[SIZE=4][COLOR=red]'[/COLOR][/SIZE]")

 

[mischa]

Thank you very very much CJ_London!!
Your my hero
The more I work with vba for Access the more I see that punctuation is critical an even one slight thing could lead to very different results :banghead:.

 

[CJ_London]

Pleased to be of assistance - computers are actually quite stupid and havesolutely no sense of understanding (at least as far as most programming languages are concerned

 

[mischa]

CJ_London, I got an follow up question regarding this case.
I would like to delete an action from a part and subsequently delete the action from tblActions.

To make this happen I think I should do the following:
Use an update qry to remove the ActionID from tblAltPart.
Something like:

UPDATE tblAltPart Set ActionID=NULL WHERE AltPN=Me.lstActionSelectPart.value

In order to remove the action completely from tblActions I therefore need to save the ActionID as an integer variable (I assume) and use it in a delete qry.

Could you advice me on how to use an variable in a delete query?

 

[CJ_London]

UPDATE tblAltPart Set ActionID=NULL WHERE AltPN=Me.lstActionSelectPart.value

yes although you do not need the Me. or .value - these are the defaults so using them has no benefit and as mentioned before AltPN is text so you should be using the single quotes

Could you advice me on how to use an variable in a delete query?

in the same way as you have in the update query - something like

mydb.execute("DELETE * FROM myTable WHERE ActionID=" & ActionID)

If you want to use SelectedID in another procedure then you can put it at the top of your module and make it public - or if in another procedure called from the current procedure you can pass it as a parameter to that procedure.

 

[mischa]

CJ_London, thanks for you quick reply !

If I put it i the same procedure as the previous it results in:

            Dim myDb As Database
            Dim rst As Recordset
            Set myDb = CurrentDb
            myDb.Execute ("UPDATE tblAltPartSet ActionID=NULL WHERE AltPN='" & Me.lstActionSelectPart.Value & "'")
            Set rst = myDb.OpenRecordset("SELECT  @@Identity as DeleteID")
            DeleteID = rst.Fields(0)
            myDb.Execute ("DELETE * FROM tblAltPartWHERE ActionID=" & DeleteID & "")
            Set rst = Nothing
            Set myDb = Nothing

I'm not sure if all should be the same because I suspect that the ActionID (DeleteID) should be saved first before it is set to NULL. In which case the procedure becomes:

            Dim myDb As Database
            Dim rst As Recordset
            Set myDb = CurrentDb
            Set rst = myDb.OpenRecordset("SELECT  @@Identity as DeleteID")
            DeleteID = rst.Fields(0)
            myDb.Execute ("UPDATE tblAltPartSet ActionID=NULL WHERE AltPN='" & Me.lstActionSelectPart.Value & "'")
            myDb.Execute ("DELETE * FROM tblAltPartWHERE ActionID=" & DeleteID & "")
            Set rst = Nothing
            Set myDb = Nothing

But I am not very familiar with using OpenRecordset. Therefore I do not know if it can be used in this way.