I need help in form on load vb script

[rajamuzammil]

I have a form when it loads, it fetches a number and increments the last stored invoice number.

UG added Code Tags

Private Sub Form_Load()
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "GRNDatatmpIni"
  Me.Refresh
 Me.Command42.Enabled = False
 Dim MyDb As Database
 Dim mFil03 As Recordset
 
 Set MyDb = DBEngine.Workspaces(0).Databases(0)
 Set mFil03 = MyDb.OpenRecordset("Fil03", dbOpenDynaset)

 If Not mFil03.BOF Then
  mFil03.MoveFirst
 End If
 mFil03.MoveNext
 
  If mFil03!CInvKey = "N" Then
   MsgBox "You can not make Invoice at this moment ..........", vbCritical
   DoCmd.Close
  End If

Me.InvNumber = mFil03!whdocno + 1
 

 
 mFil03.Edit
 mFil03!CInvKey = "N"
 mFil03.UPDATE
 mFil03.Close
 Me.Check52.Value = True
 Me.Check54.Value = False
 
Exit Sub

End Sub

The table from which this is named as Fil03, based on WHSECID in that table i want this form to fetch number. How can i do this by changing something in the code given above. Your help will be highly appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

What is your code doing now instead?

 

[rajamuzammil]

Its returning a number incremented by 1 on form load. But i want it to fetch a number from 2nd row of that table Fil03.

 

[theDBguy]

Its returning a number incremented by 1 on form load. But i want it to fetch a number from 2nd row of that table Fil03.

Have you tried using one more MoveNext?

 

[jdraw]

Please provide an example with some values so readers understand your need.

Its returning a number incremented by 1 on form load. But i want it to fetch a number from 2nd row of that table Fil03.

Not sure, but maybe you should be basing your recordset on a query that has an order by clause.

 

[rajamuzammil]

I want it to fetch number base on WHSECID value

WHSECID WHTPDOC WHDOCNO
01 INV 50053
02 WINV 64743
03 DCFDOC 2256
03 WINV 11000
04 WINV 43000
05 WINV 22000

 

[Uncle Gizmo]

Is it me? Or are the questions getting harder around here?

Sent from Newbury UK

 

[theDBguy]

I want it to fetch number base on WHSECID value

WHSECID WHTPDOC WHDOCNO
01 INV 50053
02 WINV 64743
03 DCFDOC 2256
03 WINV 11000
04 WINV 43000
05 WINV 22000

Also, just to keep things simpler, have you considered trying out using DMax() instead? Just a thought...

 

[jdraw]

Is this what you are trying to do? Instead of referring to number, use the actual field name.
I don't see a field with a name like last stored invoice number.

Select WHDOCNO from Fil03
WHERE WHSECID = "02"

For Tony
Seems to be more fill in the blanks or guess what I'm thinking.. maybe it's just a new year???

 

[rajamuzammil]

WHDOCNO is the last invoice number. There are several sections for each invoice type, section are listed as WHSECID.
Is it possible to add statement in vba code in event procedure?

 

[theDBguy]

WHDOCNO is the last invoice number. There are several sections for each invoice type, section are listed as WHSECID.
Is it possible to add statement in vba code in event procedure?

Hi. Looks like DLookup() might work better for you then.

 

[rajamuzammil]

It didn't work out mate.
Can you add those lines into my code, it would be helpful for me.

 

[jdraw]

Info and examples re DLookup()

 

[theDBguy]

It didn't work out mate.
Can you add those lines into my code, it would be helpful for me.

Actually, I was thinking DLookup() would replace all your code. So, use DLookup() (instead of your code) to get the number from the table for you, and you can then do whatever you want with it.