Question updating a table from a form

[token_remedie]

basically I've built an asset management db but when an asset gets redistributed I want to add it to the redistribution table with a yes\true value in the check field so that the next form can find that value and add the current location to it and then update the deployed (main table) table with the new location. i hope that makes sense, it runs but doesn't check the box, I'm using access 2007 I'm a bit of a noob but here's my code:

Public Sub Command101_click()


Dim SQL As String
'this part definitely works
SQL = "INSERT INTO redistributed([asset number],[Asset Description],[serial no],[Invent No])VALUES('" & Item & "','" & [Asset Description] & "','" & [Serial No] & "','" & [Invent No] & "')"

'this runs but doesn't check the box i want it to, i've tried 0,-1, true false, yes\no

If (SQL = "Select redistributed.check,residtributed.[asset number] From redistributed redistribued.check WHERE redistributed.[asset number] = [FORMS]![asset details]![item] and redistributed.check = no") Then
SQL = "UPDATE redistributed SET redistributed.check = yes"
Else: If (SQL = "Select redistributed.check,residtributed.[asset number] From redistributed redistribued.check WHERE redistributed.[asset number] = [FORMS]![asset details]![item] and redistributed.check = -1") Then MsgBox ("already relocated")

End If
CurrentDb.Execute SQL

DoCmd.OpenForm "redistlocation", , , "[Forms]![Asset Details]![Item]=" & Me![Asset Number]


End Sub

 

[jdraw]

Perhaps you could step back from the code and tell us what you want to accomplish.

It doesn't appear that you have a field called Check in your table redistributed.


Also, when you are constructing SQL strings in vba, and you are using values from Forms, you must concatenate the Form control values to the sql string.

For example, for illustration only,

SQL = "Select redistributed.check,residtributed.[asset number] From redistributed " _
       & " redistribued.check " _
& " WHERE redistributed.[asset number] =" &  [COLOR="Indigo"][FORMS]![asset details]![item] [/COLOR]_
 & " AND  redistributed.check =  True"

[FORMS]![asset details]![item] will be evaluated first and then concatenated to the sql string. The way you have it constructed will treat the [FORMS]![asset details]![item] as a constant and it will not be evaluated.

 

[token_remedie]

pretty much all I'm doing is:
I have one table which the main form is built off, then i have a redistributed table which is every asset that's been redistributed t hat feeds a subform on the main form based on form!asset details!item (which is the current record asset number).
so all im doing is, if an asset is to be redistributed it does the following:
1. check is record exists in redistributed table, if it does then is redistributed.check ticked? if no then
2. copy the current record into the redistributed table and marks that record by setting redistributed.check to true (its a yes/no value) and inserting the location into old location
3. opens a new form to enter the new location details
4. find the newly entered asset number in redistributed with the check = true and assign the new location to the redistrubted new location as well as deployed.location so it shows back on the main form as the new location

then ive got it to requery the main form to refresh the asset details on location form close

now that ive listed it out like that its way more complex than i thought it was....

 

[jdraw]

Still too code oriented for me. What do you want to accomplish in plain english-- forget form and subform.
eg.
I have equipment. Some gets redistributed. I want to record ... in order to kkow where is...
If you had 30 seconds to tell someone you didn't know, what would you tell them?

 

[token_remedie]

its just managing assets. I need to record the movement of an asset, thats pretty much it and be able to search by any aspect of the asset, e.g asset number, warranry date current location, when it was distributed etc.

 

[jdraw]

Is this model representative of what you're doing? You may need some more details for each asset..

http://www.databaseanswers.org/data_models/inventory_of_it_assets/index.htm

 

[token_remedie]

just in case anyone is wondering I ended up working it out:

<code>
Public Sub Command101_click()

Dim SQL As String
Dim chk As String

'checks for duplicates between the two tables

SQL = "SELECT deployed.* FROM deployed LEFT OUTER JOIN redistributed ON redistributed.[asset number] = deployed.[asset number] WHERE redistributed.[asset number] = & Me![Item]"

If DCount("*", "redistributed", "redistributed.[asset number]=" & Me![Item] & "And redistributed.check = 'redistributed'") > 0 Then

'msg box if it finds it

MsgBox ("This asset has already been distributed")
Exit Sub
Else
MsgBox ("Not yet Distributed")

'string creation and insert

chk = "redistributed"
SQL = "INSERT INTO redistributed([asset number],[Asset Description],[serial no],[Invent No],[check])VALUES('" & Item & "','" & [Asset Description] & "','" & [Serial No] & "','" & [Invent No] & "', '" & chk & "')"
DoCmd.OpenForm "redistlocation", , , "[Forms]![Asset Details]![Item]=" & Me![Asset Number]
End If


CurrentDb.Execute SQL



End Sub

</code>