Move inventoryrecords easily

[dcorleto]

I have db with tables: assets, employees, locations. They are all related, and somehow I've got forms actually working where I can see assets by location, or by employee. Ex. search for an employee and see what assets they have in a subform.

My question: what is the best approach now to program in when moves occur, that is, assets are moved from one location to another/to another employee?

Thanks in advance
dan c

 

[TheAceMan1]

How are ya jaaret . . .

Although we havn't seen [blue]table relationships[/blue] yet it may be as simple as an [blue]Update Query[/blue] which [purple]updates the LocationID per associated AssetID specified![/purple] . . .

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

 

[JoeAtWork]

Do you need to keep a history of all the locations the items were moved to, or is just knowing the current location sufficient?

 

[dcorleto]

Don't need history -

I create a table with Primary key for Location, then created relationship. On the subform, I've changed location to a combobox, and selected source of the primary key table. It then gives me a drop box to pick from and I'm selecting the new location that way - then I put a command button on the form to update the form. I

t seems to working so far, rudimentary - to serve as a way to move things FROM a location -Now I have to also figure out how to move assets TO the location.

 

[dcorleto]

Would be great if I could just have a form that shows this:

Enter Asset Tag: XXXXXXX

Enter New Location: XXXXX

and then a command button to do the rest.

That sounds so simple, yet sounds like it would be alot of work to do somehow.

 

[JoeAtWork]

As Aceman suggested, is your Assets table something like:

Assets
AssetID
LocationID

If not, can you show us what your table designs are like?

 

[dcorleto]

Have ASSETS table:

Tag
Asset
Location
Model
Serial
Received
Misc

Have 2 more tables

TAG - only one field and it's primary indexed no duplicates

LOCATION - only one field and it's primary indexed no duplicates

Have both of these related to appropriate fields in ASSETS table.

Am I on the right track?

 

[JoeAtWork]

So assuming you have a form with two unbound textboxes, txtAsset and txtLocation, and a button cmdMoveAsset. Then for the click event of the button, the following code:

Private Sub cmdMoveAsset_Click()
  Dim SQL As String

  SQL = "Update Assets Set Location = '" & txtLocation & _
   "' Where Asset = '" & txtAsset & "'"

  CurrentDB.Execute SQL
End Sub

I'm assuming both the Location and Asset fields are strings. If they are numbers, remove the single quotes.

 

[dcorleto]

This is where I get lost - no SQL experience. Based on your suggestion I created a form. Named the text boxes. Made the command button, event prodecure and put in the following code:

FORM_ID_358989923 is actually the full name of the ASSETS table. What/where did I miss putting something in?

Private Sub cmdMoveAsset_Click()
Dim SQL As String

SQL = "Update FORM_ID_358989923 Set Location = '" & txtLocation & _
"' Where Asset = '" & txtAsset & "'"

CurrentDb.Execute SQL
End Sub

 

[dcorleto]

On my, I think that worked - can it be? How'd you do that????

 

[dcorleto]

Getting there slowly -

on the form, I have the subform that contains the assets. In that subform the Asset Tag column appears. It is related to another table TAGS that contains all the asset tag numbers. Is it possible to add a record while in the subform and have it append the primary Tag table?