Direct update

[jmc014]

Hi,

I have a small DB which is supposed to handle in-going and out-going entries for consumables, for this I have two tables [In] and [Out]. Here below is a brief table structure:

[In] table:

Id = Text (Auto increment field (not autonumber))
Document_In = Text
Part_No = Text
Items = Number

[Out] table:

Id = Text (automatically taken form the [Id] field [In] table)
Document_Out=Text
Part_No = Text
Items = Number

The main form [Register] holds all the data from the in table and has a subform with several fields from the [Out] table. Whenever a user modifies the data in the [Out] subform the [Id] is taken so’s that we can keep a relation between the two tables.
There are occasions when we need to update various entries in the [Out] table from this main screen.

One sloppy solution that has been used is to open a form called “Update” in AcIcon which is related to a query, update the said fields, goto next text record, and update again before closing the form:

n = DCount("Id", "MyQuery")
Check = True: Counter = 0
Do ' Outer loop.
DoCmd.OpenForm "Update", , , , , acIcon
Do While Counter < n
Counter = Counter + 1

Forms![Update]![Part_No]= Forms![In_Form]![Part_No]
Forms![Update]![Items]= Forms![In_Form]![Items]

If IsNull([Forms]![Update]![Id]) Then Check = False
DoCmd.GoToRecord , , acNext

If Counter = n Then
Check = False
Exit Do
End If
Loop
Loop Until Check = False
DoCmd.Close acForm, "Update"



I would really be grateful if someone could help me create a routine that can loop through and update the fields without having to go through the above sloppy process.

Thanks,

 

[dhookom]

It isn't clear what you are attempting to update and why. I would first attempt to run an update query. If this wasn't possible, I would open a recordset to update the records. The final option I would consider is to open a form bound to the records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmc014]

Hi, I would like to update the out table (Deposito_Temporal_Salida) directly without having to use an update query or opening forms:

I have found a module that Im trying to modify myself but I'm afraid that I havent got too much of an idea:



Function Update() As Variant
Dim db As Database, Salida As Recordset, Partida As Recordset, N As Integer


N = DCount("Autorización", "TG_00000_Entrada")


Dim Campos() As Long

ReDim Campos(0)
Set db = DBEngine(0)(0)

Set Salida = db.OpenRecordset("Deposito_Temporal_Salida")
Set Partida = db.OpenRecordset("Deposito_Temporal_Salida")


For N = 1 To 1

'for each bonus we need a taxisvc
Partida.AddNew

Partida("Id") = Forms!Registro_DT!Idd
Partida("Autorización") = Forms!Registro_DT!Autorización
Partida("Fecha") = Forms!Registro_DT!Salida_Subform!Fecha
Partida("Documento") = Forms!Registro_DT!Salida_Subform!Documento


Salida.MoveNext
GoSub updateRestOfFields


Partida.Update
Next


GoTo fin

updateRestOfFields:
If Campos(0) <> 0 Then ReDim Preserve Campos(UBound(Campos) + 1)
Campos(UBound(Campos)) = Partida("Autorización")



Return


fin:

End Function


We have a query called "TG_00000_Entrada" that can give us 5 records let's say, the idea is that the code that I'm trying to create will
Add a new record (five times), take some data from the "TG_00000_Entrada" query and update the [Out] table "really called Deposito_Temporal_Salida".

The code above is only something that I'm playing around with and while It does create a new entry in the out table It only takes data form our main form.
In my previous post I said that the data only had to come from our main form, but I have just realized that we have to get data form this form "Registro_DT"
and also form the query "TG_00000_Entrada".


I hope that this is a little more clear.
Sorry for changing the table names and fields, but the BD is in Spanish and I thought it might be easier if I changed the Table names and fields for everyone
understand the post.

Thank you for your time,

 

[dhookom]

I am having trouble understanding your code. First, I expect you are using DAO so your code should be like:

Dim db As DAO.Database
Dim Salida As DAO.Recordset
Dim Partida As DAO.Recordset
Dim N As Integer

Your two recordsets are exactly the same. I don't think you are using the Salida recordset. Also, none of your code includes Registro_DT.

If I had to append a given number of records to tableB based on single record in tableA, I would:
[li]Create a table of numbers tblNums with a single numeric field [num] with values 1 - the maximum number you will ever need[/li]
[li]create a query based on tableA and tblNums[/li]

SELECT FieldA, FieldB, FieldC
FROM tableA, tblNums
WHERE tblNums.Num<=DCount("Autorización", "TG_00000_Entrada")
 AND FieldA=Forms!Registro_DT!Idd AND FieldB=Forms!Registro_DT!Autorización;

[li]Change the query to an append query to append to your tableB.[/li]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jmc014]

I have come a long way since my last post, this is what I've got at the moment which is 99% operative:

Function Update() As Variant
Dim db As Database, Partida As Recordset


Dim Text75
Dim AnyString, MyStr


Dim Check, Counter, N
Check = True: Counter = 0 ' Initialize variables.
N = DCount("Autorización", "TG_00000_Entrada")


Do ' Outer loop.

Do While Counter < N ' Inner loop.
Counter = Counter + 1 ' Increment Counter.

'**********************************************************************


Set db = DBEngine(0)(0)
Set Partida = db.OpenRecordset("Deposito_Temporal_Salida")


DoCmd.OpenForm "TG_00000_Entrada"
' DoCmd.OpenForm "TG_00000_Entrada", , , , , acHidden

Partida.AddNew



Forms!TG_00000_Entrada!Cancelado = True

Partida("Documento") = DLookup("Documento", "Contador") + 1
Partida("Id") = Forms!TG_00000_Entrada!Id
Partida("Autorización") = Forms!Registro_DT!Autorización
Partida("Fecha") = Forms!Registro_DT!Salida_Subform!Fecha
Text75 = Year(Forms!Registro_DT!Salida_Subform!Fecha)
AnyString = Text75
MyStr = Right(AnyString, 1)
Partida("Campo5") = MyStr
Partida("Campo4") = Forms!Registro_DT!Salida_Subform!Text44
Partida("Campo6") = Forms!Registro_DT!Salida_Subform!Combo56
Partida("Campo7") = Forms!Registro_DT!Salida_Subform!Combo58
Partida("Bultos") = Forms!TG_00000_Entrada!Bultos_Vivo
Forms!TG_00000_Entrada!Bultos_Vivo = 0
Partida("Kilos") = Forms!TG_00000_Entrada!Peso_Vivo
Forms!TG_00000_Entrada!Peso_Vivo = 0
Partida("Volumen") = Forms!TG_00000_Entrada!Volumen_Vivo
Forms!TG_00000_Entrada!Volumen_Vivo = 0
Partida("Valor") = Forms!TG_00000_Entrada!Valor_Vivo
Forms!TG_00000_Entrada!Valor_Vivo = 0
Partida("Matricula") = Forms!Registro_DT!Salida_Subform!Matricula
Partida("Campo3") = Forms!Registro_DT!Salida_Subform!Text47
Partida("Partida") = Forms!Registro_DT!House_BL




Partida.Update
db.Close

DoCmd.Close acForm, "TG_00000_Entrada"

'**********************************************************************




If Counter = N Then ' If condition is True.
Check = False ' Set value of flag to False.




Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False ' Exit outer loop immediately.







End Function


"TG_00000_Entrada" is that name of a query that amongst other things gives me a value for the loop routine. The only thing I have not been able to do is to update the data in this query directly without opening a form based on this query.
Do you know if there is a way to update data into a query in the same way as into a table?

Thanks a lot for your help.

 

[dhookom]

If a query is updateable then it will behave nearly the same as a table in a recordset.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]