Replies

It works!! Thank you Hans and Rudi for your patience and support. It is VERY much appreciated.

I’ve stripped out almost everything from the database. When you open the Form and press the first button ‘1. Calculate Price Each’ it should update the date/time stamp to the side of it, but it doesn’t. In this stripped down version I’ve removed the queries the button would have also executed as my problem lies with the date/time not changing.

I replaced Me with CodeContextObject.Recalc

When the button is pressed the macro now correctly runs each of my queries but at the end of this I get “Syntax error in update statement”.

Here is the code…

‘————————————————————
‘ getpriceeach
‘
‘————————————————————

Function getpriceeach()
On Error GoTo getpriceeach_Err

DoCmd.SetWarnings True
‘ 1. Add order info
DoCmd.OpenQuery “1 – ADD ORDER INFO”, acViewNormal, acEdit

‘ 1a. Group & sum
DoCmd.OpenQuery “1A – GROUP&SUM”, acViewNormal, acEdit

‘ 2. Group & max
DoCmd.OpenQuery “2 – GROUP & MAX”, acViewNormal, acEdit

‘ 3. Calculate price each
DoCmd.OpenQuery “3 – CALCULATE PRICE EACH”, acViewNormal, acEdit

DoCmd.SetWarnings True

Dim strSQL As String
strSQL = “UPDATE tblTimeStamp Set TimeStamp = Now()”
CurrentDb.Execute strSQL
CodeContextObject.Recalc
Exit Function

getpriceeach_Exit:
Exit Function

getpriceeach_Err:
MsgBox Error$
Resume getpriceeach_Exit

End Function

Now I get “Compile Error Invalid use of Me keyword”.

Edited by HansV to replace Word doc with cropped version of the Word document it contained.

Oops! Ok….I’m a bit further forward now but am getting a compile error in the VB code for the button. Please see attached screenshot.

The function found in the VBA module from the converted macro just shows as ‘Function getpriceeach() ‘. getpriceeach is the name of my macro. When I paste ‘getpriceeach()’ between the Sub and End Sub statements (for my button), I get ‘Compile Error Expected: =’.

Thanks Rudi. Sorry if this is a dumb question but I’m getting unstuck on your last step when I have to click on the elipse button. Please clarify what sort of thing I should be typing and exactly where. Thanks.

The On Click event procedure of the command button is already set to the name of my macro that runs a series of queries. Where should I put the code?

Ok. Thanks for clarifying that. Appreciate your time.

Thanks, that worked. Is there any other way of making this formatting happen during the query stage? I was hoping to have put my MTQ along with 12 other queries on a single button with a macro that would run the whole lot. Any ‘tweaking’ with a table after its creation would be a problem.

The MTQ Format property of the field is already set to fixed.

Ok. I seem to be a little closer. The resulting MTQ table now shows the prices but if its a whole number like ‘128.00’, this appears on my table like ‘128’. If the price is ‘6.50’ it shows on my table as ‘6.5’.How can I make sure the decimal point is always shown and with two digits after it? Many thanks.

Tbl 1 contains stock balances for hundreds of parts.
Tbl 2 contains a list of part numbers that I’m interested in for a certain exercise.
Tbl 3 contains prices for each of the parts in tbl 2. The table containing two fields – PartNumber and Price. The data type of the ‘Price’ field is Number, Field Size Double, Format Fixed, Decimal Places Auto. The prices show correctly in this table (e.g. 125.85). I don’t need to see the currency symbol.
My make table query links tbl 1 part number to tbl 2 part number (join property 1). The fields I want to see in the table it produces come from tbl 1 as well as some new fields I create that will be populated later by several update queries. One of the new fields is ‘PriceEach’. I input this as ‘PriceEach: 0’. So initially when the MTQ is created, this field shows zeros and is identified as a data type of Number.

When I later run an update query to take the price from tbl 3 and populate them in my MTQ (against the correct part numbers), the formating is all wrong. The prices are shown without any decimal point. Where am I going wrong?

I don’t think an append query can help me. I have data from multiple tables being updated into a single table that has been created by my MTQ. I will keep ‘playing’ and see what I come up with. Thanks anyway.

Thanks for such a quick reply. Which table field show I be amending? The MTQ or the table the prices originates in?