Update Statement 2

[edgarchado]

Hi all,

I am Having trouble with an update statement.

I have a form with a list which populates through a simple search. Once It populates, I double click on one of the items.

When I double click, I would like to update a record in a table based on records in another table.

I use the following statement.

SQL2 = "Update tblTarjetas Set Ultima_Entrega_a = tblHeladera.Entregada_a where (Select tblheladera.Entregada_a From tblHeladera where N_Tarjeta = " & Me.lstPedido & " and tblHeladera.Reingreso <> False and N_Tarjeta = " & Me.lstPedido & ";"
DoCmd.RunSQL SQL2

 

[Remou]

Hi
You do not mention the problem / error. For example, is Me.lstPedido returning the number you expect?

 

[edgarchado]

Remou,

You are right, I completely forgot.

The problem is when the query executes it asks for the value of the tblHeladera.Entregada_a parameter. I don't want to enter it manually I want for access to look for it in the tblheladera table.

I though that by adding the where (Select tblheladera.Entregada_a From tblHeladera where N_Tarjeta = " & Me.lstPedido & " and tblHeladera.Reingreso <> False clause acces would find the value.

I don't know how to link tblheladera.entregada_a with the nested select statement.

Thanks in advance

 

[Remou]

I am afraid you have me beat.
I would have done it in two steps, first get tblheladera.Entregada, then update tblTarjetas. However, this forum:
Microsoft: Access Queries and JET SQL Forum
forum701
Attracts some very smart people.

 

[edgarchado]

Remou,

I tried to do it in two steps, but with no luck.

How would you do it?

Thanks

 

[Remou]

Like this:

strEntregada = DLookup("Entregada_a", "tblHeladera", _
    "N_Tarjeta = " & Me.lstPedido & " and Reingreso<>False")

SQL2 = "Update tblTarjetas Set Ultima_Entrega_a = '" & strEntregada _
& "' Where N_Tarjeta = " & Me.lstPedido & ";"
DoCmd.RunSQL SQL2

Assuming that Ultima_Entrega_a is a text field.

 

[edgarchado]

Remou,

It works like a charm. Thanks a lot.

Here a Star for you.

By the way, so you know a way to aling the text in a list box to the right, not necesarily using VB.

Thanks

 

[Remou]

Glad it worked out. I don't know how to align text in a listbox, but this guy:
Right align values in a list box
thread702-473919
Does.

 

[edgarchado]

Remou,

Thanks. I will give it a look.

Edgar

 

[edgarchado]

Remou,

I have another problem.

I would need to do two different tasks.

1 - Get the value from tblheladera when me.lstpedido exists in this table and update it in tbltarjetas table, and
2 - Ask for the value to update in tbltarjetas when me.lstpedido is not in tblheladera. (maybe through a INPUT BOX)

Is this possible? maybe through an if then else statement?

Thanks in advance and I hope I am clear Enough.

 

[PHV]

Dim strEntregada As Variant
strEntregada = DLookup("Entregada_a", "tblHeladera", _
"N_Tarjeta = " & Me.lstPedido & " and Reingreso<>False")
If IsNull(strEntregada) Then
strEntregada = InputBox("Your prompt here", "Your title here", "Your default value here")
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[edgarchado]

PH,

That was what I was lookin for. Much Apreciated.

I have one question though. It works great, but I put the:

If IsNull(strEntregada) Then
strEntregada = InputBox("Your prompt here", "Your title here", "Your default value here")
End If

part of the code within a message code, the value is not updated in the table.

Any ideas?? Here is my code. The Input box pops up, but it doesnt update the data

Thanks.

Private Sub lstPedido_DblClick(Cancel As Integer)

Dim SQL As String, SQL2 As String, strEntregada As Variant, Msg As String, Style As String, Title As String

SQL = "Update tblTarjetas Set Entregada = True where N_Tarjeta = " & Me.lstPedido & ";"

strEntregada = DLookup("Entregada_a", "tblHeladera", _
"N_Tarjeta = " & Me.lstPedido & " and Reingreso=False and Cancelada=False")

SQL2 = "Update tblTarjetas Set Ultima_Entrega_a = '" & strEntregada _
& "' Where N_Tarjeta = " & Me.lstPedido & ";"

Msg = "Dar Salida a la Tarjeta N°" & Me.lstPedido & "?"
Style = vbQuestion + vbYesNo
Title = "Salida de Tarjeta"
Beep

If MsgBox(Msg, Style, Title) = vbYes Then
DoCmd.SetWarnings False
If IsNull(strEntregada) Then
strEntregada = InputBox("Tarjeta Entregada a:", "", "")
End If
DoCmd.RunSQL SQL
DoCmd.RunSQL SQL2
Else
End If

End Sub

 

[PHV]

Move the following lines just before the DoCmd.RunSQL:
SQL2 = "Update tblTarjetas Set Ultima_Entrega_a = '" & strEntregada _
& "' Where N_Tarjeta = " & Me.lstPedido & ";"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[edgarchado]

Ph,

Thanks For the Quick response.

It is working great.

Here is a star for you.

Thanks,
Edgar.