How to use "me" in macro form

[cheese]

Before I say anything - I cannot use VBA; I must use Macros. I do not have a choice.

Could someone please tell me how I can update a relevant table record based on the id number that it relates to from a form? I will explain further:

I have a rental input form for renting stuff. Part of the rental process involves me informing another table (The table with the stock number information) that the item in question is "OnLoan". That OnLoan value is simply a boolean. I am aware of how easy this is to do in VBA... I would simply use me.StockNo. Unfortunately, because we have been forced to use Macros it is not so easy.

I just need to use the same kind of "me" format. I just need some way of passing that value from the form and update the other table WHERE StockNo = the value from the form. Any help would be appreciated. It is just such a simple thing that I need to do and I cannot find this information.

 

[pbaldy]

Unless I'm missing something, you need the full form reference:

Forms!FormName.StockNo

replacing the form name in red with yours.

 

[cheese]

That almost looked like it was going to work . I think you may have resolved the problem partially. TY.

It just says it is going update 0 records now. It has been placed within criteria of the query. Also, when I look at it in SQL it looks like it should be correct but it does not update anything. Like I said it offers to update the record and then says "You are about to update 0 rows". Well, thank you Access for the really useful advice

Maybe there is something else wrong. I have used -1 as a "true" value (for whatever reason MS has for using that). It worked when I did it in VBA anyway.

 

[pbaldy]

That sounds like a query rather than a macro. Can you post the db here?

 

[cheese]

I thought that that was what I was supposed to do. I created the update query and then opened it with the macro. Can I simply update the other table with the macro on its own? Hopefully I can try to work this out with out posting the DB.

 

[pbaldy]

It sounds like the form reference is in the query though, correct? What is the SQL of the query? Is the value in the textbox found in the data? Make sure you've tabbed out of the textbox so its value can be seen by the query.

 

[cheese]

Ok I have changed it somewhat so that it attempts to rename a supplier with some test text data. The query is basically:

UPDATE GameCopy SET GameCopy.Supplier = "test1"
WHERE GameCopy.Supplier =[Forms]![Rental].[StockNo];

For some reason the query designer put in bunch of unneeded brackets so I took them out for clarity. This one is also still just coming up with the message saying that it will update 0 records.

The macro is simply an OpenQuery: Queryname
View: Datasheet
Datamode: Edit

 

[Galaxiom]

WHERE GameCopy.Supplier =[Forms]![Rental].[StockNo];

That is a very unlikely looking Where clause.

Supplier and StockNo seem like they would be very different things.

 

[cheese]

OMG!!!!!!!

Ok, it just must be getting late and I am tired and depressed after all this messing around. I did not spot it. This is the reason why I like to write the code myself and not use these silly query designer things

Apologies and thank you.