Update doesn't work everytime? (1 Viewer)

Hi all!

I'm having a problem with an update query run from vba. it sometimes does what it's supposed to do and sometimes it doesn't...

It just changes an ID to another

strSql = "UPDATE tblInvEquipos SET EquipoIdFk=" & dblAsignarId & " WHERE InvEquiposIdPk=" & dblInvEquipoId & ""
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

dblNewId = DLookup("EquipoIdFk", "tblInvEquipos", "InvEquiposIdPk= " & dblInvEquipoId & "")
strEquipoNuevo = DLookup("Equipo", "tblEquipos", "EquipoIdPk=" & dblNewId & "")

I even put the last lines to check that it did it but the message displays the right info but sometimes it doesn't changes anything!

any thoughts?

Thank you!

Why exactly are you doing this?
Can you show us your table designs? Relationships window?

You have to zip then attach using options available after clicking the GoAdvanced Button.

Hi jdraw,

it's an inventory. The items are assinged to Teams. this is to change the item from stock to a team or ftom team to team.
the tables in question are:

tblEquipos 'the teams table
EquipoIdPk 'AutoNumber and IDPK
Equipo 'the name of team

tblInv 'Inventory table
InvIdPk 'AutoNumber and IDPK
ProvIdFk 'Id from the supplier

tblInvEquipos 'join between tblEquipos and tblInv
InvEquiposIdPk ''AutoNumber and IDPK
InvIdFk 'Foreing key from tblInv
EquipoIdFk 'Foreign key from tblEquipos

they select the current team from a combo box:
dblActualId = Me.cboEquipoActual.Value

the team to change to from another combo box:
dblAsignarId = Me.cboEquipoAsignar.Value

the id from tblInvEquipos i get from a query:
dblInvEquipoId = DLookup("InvEquiposIdPk", "qryAsignar") 'this query works fine, it gets the values from suppliers, brand, model and rerturns the id.

then i run the query:
strSql = "UPDATE tblInvEquipos SET EquipoIdFk=" & dblAsignarId & " WHERE InvEquiposIdPk=" & dblInvEquipoId & ""
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

when i watch step by step it works fine but sometimes it doesn't while i'm not watching the code run...

thanks!

Is this a desktop application? If so, can you post a copy of the database (zip format)?

What exactly is a team?

You seem to be missing a/some table(s) - suppliers, brand, model
It would be better for readers to see your table relationships. And to describe how your tables relate to each other in business terms.

I have attached a jpg(model) of what I think your post described.

Hi jdraw,

A team is the seales persons (2 per team), we assign a team number to keep track of who has the items in their inventory.

the other tables i have them but are not important to the issue.

in the pic you can see the relationships.

Thank you!

Aceavl said:

..
when i watch step by step it works fine but sometimes it doesn't while i'm not watching the code run...

Click to expand...

For me it sounds like a speed issue, I would try to put in a DoEvents just before the SQL-String:

As an alternate you could try:

Hi all!

JHB. I tried with DoEvents and no luck...

burrina: I'll try your method and let you know if it worked.

Thank you!

Hi all,

I solved it thanks to burrina! when I watched the RecordSet the query was returning 2 record in some instances and it updated the wrong record, that's why the record in question wasn't updated!

Thank you for help!