PLS HELP: Change the value of table using DoCmd

[oltroman]

Hi,

Private Sub Toggle21_Click()

Dim StrBarcode As String
Dim StrJob_No As String


StrBarcode = Forms!Outgoing.oBarcode '=9 (for example)
StrJob_No = Forms!Outgoing.oJob_No '=Test (for example)


'Open Marketing Form
DoCmd.OpenTable "Inventory"

'Add new record OK up to here
DoCmd.GoToRecord , , acGoTo, StrBarcode 'goto line 9

'---------------------------------------------------------------------------------

'Set Job_No value
CurrentRecord.Job_No = StrJob_No


End Sub

I got error on

CurrentRecord.Job_No = StrJob_No

Anybody know how to set Job No field in that table to StrJob_No?

yes, I copy this code from other thread

 

[The John Rambo]

'Open Marketing Form
DoCmd.OpenTable "Inventory"

I see that you are entering the data into the table and not via a form.

Try replacing:

'Open Marketing Form 
DoCmd.OpenTable "Inventory"  

'Add new record OK up to here 
DoCmd.GoToRecord , , acGoTo, StrBarcode 'goto line 9 

'--------------------------------------------------------------------------------- 

'Set Job_No value 
CurrentRecord.Job_No = StrJob_No

with...

'Adds a new record to a table and inserts data
'Put your table name in quotes
Set RstInv = CurrentDb.OpenRecordset("Inventory") 
With RstInv
      'Adds the new record
     .AddNew
      'Put the [B]field[/B] you update and the [B]data[/B] you want to enter 
     .Fields("Job_No").Value = StrJob_No 
      'Updates the newly added record
     .Update 
End With

 

[oltroman]

I see, so have to use DAO

StrBarcode value is the Primary key for the lines that I want to edit for examples line "9"

With DAO how do I go to line 9 for example? and then Edit Job_No field. I don't want to AddNew. Thank you very much. Yes I am noob in VBA

 

[oltroman]

I use this code

Set RstInv = dbInv.OpenRecordset("Inventory")
With RstInv
      .GetRows ([StrBarcode])
      .Edit
      
      'Put the field you update and the data you want to enter
     .Fields("Job_No").Value = StrJob_No
      'Updates the newly added record
     .Update
End With

Only 1 problem, this edit the row that's below the one that I want. Not the row that I want to edit. Anybody help?

If I put

StrBarcode = StrBarcode -1

Then when I search the first line that I got error because now StrBarcode = 0.

 

[oltroman]

One more thing that bother me, I want to find a new line that's empty and add value to it (in this case date)

Set RstInv = dbInv.OpenRecordset("DateRecord")
With RstInv
    .FindLast  'I got error here why????
    .AddNew
    .Fields(BarcodeL).Value = Forms!IN_OUT.oInOut & Date
    .Update
End With

Much appreciate any input

 

[The John Rambo]

You can move to the previous record by inserting .Moveprevious

Set RstInv = dbInv.OpenRecordset("Inventory") 
With RstInv       
     .GetRows ([StrBarcode])       
[COLOR=Blue] [COLOR=Lime] [COLOR=Red]   .Moveprevious[/COLOR][/COLOR][/COLOR]
     .Edit

It may not be the best method but it'll work.

You got an error because you need to specify criteria.

.FindLast  'I got error here why????

e.g.

.FindLast ("isnull([BarcodeL])")