Variables become empty

[jbleal21]

I have this database to control the spares in several places(Obras), in the movimentos 2 form we introduce if is a out (saida) or entry (Entrada), we put the place (Obra), for that movement we have several ferramentas (tools), we put the tool (ferramenta), the quantity. After i want to update the stock of each tool, for that I created a form Movimentos e Ferramentas Atualizar Stock, that is to create 2 movements (1 to update the warehouse (armazem) and other to update the place with the stock of that tool).
Still didnt put the code to create the records.
the code is this:
Private Sub Command25_Click()
'Dim tipomov As String
'Dim UltimoIMOVFerra As Long
'Dim Obra As Long
'Dim Armazem As Long
'Dim Data As Date
'Dim IDMOV As Long
'Dim IDObra As Long
'Dim IDFerramenta As Long
'Dim Quantidade As Long


Obra = Me![IDObra]
Armazem = 2
Data = Me![Data]
IDMOV = Me![IDMovimento]

tipomov = Me![TipoMovimento]
Me![Movimentos e Ferramentas].SetFocus
DoCmd.GoToRecord , , acLast

UltimoIMOVFerra = Forms![Movimentos 2]![Movimentos e Ferramentas].Form![IDMOVFerra]


DoCmd.GoToRecord , , acFirst

Do Until UltimoIMOVFerra <> Forms![Movimentos 2]![Movimentos e Ferramentas].Form![IDMOVFerra]



If tipomov = "Saída" Then

IDFerramenta = Forms![Movimentos 2]![Movimentos e Ferramentas].Form![IDFerramenta]
Quantidade = Forms![Movimentos 2]![Movimentos e Ferramentas].Form![Quantidade]



DoCmd.OpenForm "Movimentos e Ferramentas Atualizar Stock", acNormal


End If

If tipomov = "Entrada" Then

DoCmd.OpenForm "Movimentos e Ferramentas Atualizar Stock", acNormal

End If

If tipomov = "Inventário" Then
DoCmd.OpenForm "Movimentos e Ferramentas Atualizar Stock", acNormal
End If


Loop

End Sub

But when pass the until instruction the variables lost their value.
I defined that variables in a global module.
Can someone help me?
Thank you.

 

[arnelgp]

Firstly you are opening the form without closing it.
Second, what are the forms for?
You can directly update the table using update query.

 

[jbleal21]

Dear Arnelgp:
Thank you for help.
I want to open that form to create 2 records in the Ferramentas Obras Stock tabel.
1 record of entry or out in the warehouse (an IDOBra) of the tools quantity, and 1 record of entrey or out in the work place (other IDObra).
I dont know to update the table directly.
The tabel is Ferramentas Obras Stock

IDFerraStock autonumber - key
IDFerramenta(tool) number - key
IDObra(place) number - key
IDMOVFerra(id of the tool movement) - number - key
IDMovimento(id of the movement - that has several tools movements) - number
StockInicial - initial stock
StockFinal - final stock

Each movement (Movimentos) will have several IDMOVFerra (movements of tools), related to a place - IDObra -
with a quantity (quantidade), and each IDMOVFerra will have to update stock values, 1 in the warehouse(have a idobra)
can be a entry or out, and in the other place (other IDObra) will have alsoan entry or out.

 

[The_Doc_Man]

First, if you don't know how to update a specific table directly, may I suggest that you look at articles for "INSERT INTO" as a way to do that?

If you know all the values to be inserted, try some variant of the code below. It COULD be as simple as to build an INSERT INTO action query in a string variable, then executing the query. If you take this approach, the list of values has to be in the same order as the list of fields to be updated. For string variables, there can be quoting issues. Use the SEARCH function of this forum to look up more on INSERT INTO queries, include some examples of their use.

strINSQRY = "INSERT INTO {name-of-table-goes-here} ( {list-of-fields-goes-here} ) VALUES ( {list-of-literal-values-goes-here} ) ;"

CurrentDB.Execute strINSQRY, dbFailOnError

The fail-on-error option means if something is wrong with the query it will do an automatic rollback (thus leaving the table unchanged) and will also signal an error.

You could also do a DoCmd.RunSQL on that string, and if you are unfamiliar with that method, you can look it up as well.

There are advantages and disadvantages to both the .Execute and the .RunSQL, mostly speed vs. flexibility types of trade-off.

You can use a query instead of a table for the destination as long as that query is updateable. (Some are not, particularly if they contain aggregated functions).

Second, you suggest that the global variables are losing their values. Can you do a project-level string search (from the VBA code window) for those variables to see where they are referenced in code to see if some other module or routine is causing the problem?

The only two ways I know of to cause a global variable to lose its value are

(a) manually doing a RESET (click the square button in the code-window menu bar, second item to the right of the right-pointing triangle that is the RUN button) or

(b) using code to reset the individual variables.

I searched for (c) a VBA wholesale method to do a project reset, but I didn't find one. It doesn't mean there isn't one, but if it exists, it is obscure. Therefore I doubt you found one by accident.

To be honest, I didn't expect to find one, since that would be somewhat like the old Looney Tunes cartoon meme of sawing off the branch you are sitting on and expecting the tree to fall down instead of you falling down.

 

[Pat Hartman]

Also, your code refers to variables that are not defined. The Dim statements are commented out.

IDMOV = Me![IDMovimento]

 

[jbleal21]

The variables are defined in a global module as public

 

[arnelgp]

Can you post a sample db. Its easy to see the big picture.

 

[jbleal21]

hi,

i put this code:
INSERT INTO Ferramentas_Obras_Stock (IDFerramenta,IDObra, IDMOVFerra, IDMovimento)
VALUES (1, '1', '1','1')
but gives a compile error in Ferramentas_Obras_Stock

First, if you don't know how to update a specific table directly, may I suggest that you look at articles for "INSERT INTO" as a way to do that?

If you know all the values to be inserted, try some variant of the code below. It COULD be as simple as to build an INSERT INTO action query in a string variable, then executing the query. If you take this approach, the list of values has to be in the same order as the list of fields to be updated. For string variables, there can be quoting issues. Use the SEARCH function of this forum to look up more on INSERT INTO queries, include some examples of their use.

strINSQRY = "INSERT INTO {name-of-table-goes-here} ( {list-of-fields-goes-here} ) VALUES ( {list-of-literal-values-goes-here} ) ;"

CurrentDB.Execute strINSQRY, dbFailOnError

The fail-on-error option means if something is wrong with the query it will do an automatic rollback (thus leaving the table unchanged) and will also signal an error.

You could also do a DoCmd.RunSQL on that string, and if you are unfamiliar with that method, you can look it up as well.

There are advantages and disadvantages to both the .Execute and the .RunSQL, mostly speed vs. flexibility types of trade-off.

You can use a query instead of a table for the destination as long as that query is updateable. (Some are not, particularly if they contain aggregated functions).

Second, you suggest that the global variables are losing their values. Can you do a project-level string search (from the VBA code window) for those variables to see where they are referenced in code to see if some other module or routine is causing the problem?

The only two ways I know of to cause a global variable to lose its value are

(a) manually doing a RESET (click the square button in the code-window menu bar, second item to the right of the right-pointing triangle that is the RUN button) or

(b) using code to reset the individual variables.

I searched for (c) a VBA wholesale method to do a project reset, but I didn't find one. It doesn't mean there isn't one, but if it exists, it is obscure. Therefore I doubt you found one by accident.

To be honest, I didn't expect to find one, since that would be somewhat like the old Looney Tunes cartoon meme of sawing off the branch you are sitting on and expecting the tree to fall down instead of you falling down.

 

[The_Doc_Man]

Verify that the table (or query, whatever it is) is updateable and spelled correctly. Here is an MSDN reference for INSERT INTO

https://msdn.microsoft.com/en-us/library/office/ff834799.aspx

To be honest, what you posted looks OK, so the question has to be whether there is a spelling error somewhere. Access DOES NOT make assumptions about the names of any user objects such as tables or fields. Or the other possibility is that your referenced item is somehow not updateable. Is that a table or a query? If it is a query, is it a simple SELECT or some sort of SQL aggregate?

When you got the compile error, what message popped up? Because that usually helps determine the nature of the problem.

 

[Cronk]

Couple of comments

I put this code:
INSERT INTO Ferramentas_Obras_Stock (IDFerramenta,IDObra, IDMOVFerra, IDMovimento)
VALUES (1, '1', '1','1')
but gives a compile error in Ferramentas_Obras_Stoc

I hope that has not been literally inserted in the code. Something is wrong with the syntax.


In #1, the code includes

'Dim Obra As Long

If the field ObraID is numeric, then the above insert is presumably trying to insert a string into a numeric field.

 

[jbleal21]

Hi, thanks.

How can insert numeric and text fields because in the all table i have these 2 type?

 

[jbleal21]

Now gives a run-time error 3134
syntax error in INSERT INTO statement

 

[The_Doc_Man]

Can you cut/paste the exact code where you define the SQL statement and use it? We need to see that exactly as you used it in order to track down the error.

 

[jbleal21]

strINSQRY = "INSERT INTO {Ferramentas_Obras_Stock} ( {IDFerramenta,IDObra, IDMOVFerra, IDMovimento} ) VALUES ( {'1', '1', '1','1'} ) ;"

CurrentDb.Execute strINSQRY, dbFailOnError

 

[pbaldy]

There shouldn't be any curly brackets. Try:

strINSQRY = "INSERT INTO Ferramentas_Obras_Stock(IDFerramenta,IDObra, IDMOVFerra, IDMovimento) VALUES ('1', '1', '1','1') ;"

The single quotes around values are for fields with a text data type. For numeric types, remove them.

 

[jbleal21]

it worked. thank you

 

[pbaldy]

No problem.

 

[The_Doc_Man]

Paul got there first. Unfortunately, my computer was occupied because we had grandson duty this evening. I guess I confused you with my use of curly braces, but that is the way we sometimes show a substitution that you have to make. Sorry if I confused you. The rest of that ought to work.

 

[Mark_]

@ Doc,

I blame it on good food and family being present...