I need help in form on load vb script (1 Viewer)

rajamuzammil

New member
Local time
Today, 05:38
Joined
Jan 11, 2020
Messages
5
I have a form when it loads, it fetches a number and increments the last stored invoice number.

UG added Code Tags

Code:
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.
 
Last edited by a moderator:

theDBguy

I’m here to help
Staff member
Local time
Today, 05:38
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

What is your code doing now instead?
 

rajamuzammil

New member
Local time
Today, 05:38
Joined
Jan 11, 2020
Messages
5
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:38
Joined
Jan 23, 2006
Messages
15,361
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

New member
Local time
Today, 05:38
Joined
Jan 11, 2020
Messages
5
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

Nifty Access Guy
Staff member
Local time
Today, 12:38
Joined
Jul 9, 2003
Messages
16,244
Is it me? Or are the questions getting harder around here?

Sent from Newbury UK
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:38
Joined
Oct 29, 2018
Messages
21,357
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

Super Moderator
Staff member
Local time
Today, 08:38
Joined
Jan 23, 2006
Messages
15,361
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.

Code:
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

New member
Local time
Today, 05:38
Joined
Jan 11, 2020
Messages
5
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

I’m here to help
Staff member
Local time
Today, 05:38
Joined
Oct 29, 2018
Messages
21,357
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

New member
Local time
Today, 05:38
Joined
Jan 11, 2020
Messages
5
It didn't work out mate.
Can you add those lines into my code, it would be helpful for me.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:38
Joined
Oct 29, 2018
Messages
21,357
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.
 

Users who are viewing this thread

Top Bottom