update table

[gray78]

I have two tables, Scantable1 and serial#. In a form, I am pulling a field from scantable1(tagno). I am inputting information into this form and want to be able to update the serial# table with the for data. I need to know how I can update the serial# table with the tagno field from the scantable1 table if it does not exist in the serial# table.

Thanks

 

[gol4]

see append query in access help

 

[Pampers]

hi gray78...
first of all, it is advisable to name your tables consistently, like tblScan and tblSerial#.

You cant update your tblSerial with the TagNo-field if the TagNo-field is not in that table. Maybe add this field to tblSerial and create a link between the tables? And go from there...



Pampers.

You're never too young to learn

 

[TheAceMan1]

How are ya gray78 . . . . .

Hard to say yet . . . . what is your [blue]table structure[/blue] & [blue]relationships[/blue]?

See Ya! . . . . . .

 

[gray78]

The tagno field is in both tables and is the primary key.
The input data being written into the form is being saved into the serial# table but I want to also save the tagno data being pulled from the scantable1 to be put into the serial# table if it is not there with associated data.

 

[TheAceMan1]

gray78 . . . . .

Not quite what I was looking for, but you can use the DLookup function to determine wether to transfer tagno. Something like this:

[blue]   Dim Criteria As String
   
   Criteria = "[TagNo] = [red][b]'[/b][/red]" & Me!TagNo & "[red][b]'[/b][/red]"
   
   If IsNull(DLookup("[TagNo]", "Serial#", Criteria)) Then
      [green]'Transfer TagNo[/green]
   Else
      [green]'What to do if TagNo exist[/green]
   End If[/blue]

If tagno is numeric, remove singe quotes on the criteria line . . .

See Ya! . . . . . .

 

[gray78]

tagno is a text field, however, still not able to get the tagno from scantable1 table to insert into serial# table.

any suggestions??

 

[TheAceMan1]

gray78 . . . .

and this:

[blue]   Dim Criteria As String, SQL as String
   
   Criteria = "[TagNo] = '" & Me!TagNo & "'"
   
   If IsNull(DLookup("[TagNo]", "Serial#", Criteria)) Then
      [purple][b]SQL = "Your Append SQL"
      Docmd.RunSQL SQL[/b][/purple]
   Else
      'What to do if TagNo exist
   End If[/blue]

See Ya! . . . . . .