Helpful Information
 
 
Category: Visual Basic Programming
Generating Field Value

Hello. I am trying to display the values of fields called 'session number' and 'session date' by clicking on a command button in Access. I would like to incorporate VB code in the click action of the button. The button is called 'New Session Yes' and once this is clicked i want to start a new session with the session number and current date displayed in fields 'Session Number' and 'Session Date'. Would i do all of this coding in the onclick command property of 'New Session Yes'? The session number field corresponds to the record number, so if the new record is record #5 then, the session number should also = 5. How would i display the value in these fields. i don't know much about vb and it's syntax.

i got this code from somone else, but i don't think it is what i want it to do.

Option Compare Database
Dim iLastSession As Integer

Private Sub New_Session_Yes_Click()
On Error GoTo Err_New_Session_Yes_Click

DoCmd.GoToRecord , , acNewRec
MsgBox "Last Session: " & iLastSession
MsgBox "Selected Session: " & Session_Number.Value

If iLastSession >= Session_Number.Value Then
MsgBox "You already chose this. Select another session.", vbCritical
Else
iLastSession = Session_Number.Value
End If
Exit_New_Session_Yes_Click:
Exit Sub

Err_New_Session_Yes_Click:
MsgBox Err.Description
Resume Exit_New_Session_Yes_Click


End Sub

someone please help! thanks.

ameen

You could do it using the CurrentRecord property to get the record number of your current record and the new record. Does something like this help?

Option Compare Database

Private Sub New_Session_Yes_Click()
On Error GoTo Err_New_Session_Yes_Click

MsgBox "Last Session: " & CurrentRecord & " " & Format(Now(), "mm/dd/yyyy")
DoCmd.GoToRecord , , acNewRec
MsgBox "Selected Session: " & CurrentRecord & " " & Format(Now(), "mm/dd/yyyy")

Exit_New_Session_Yes_Click:
Exit Sub

Err_New_Session_Yes_Click:
MsgBox Err.Description
Resume Exit_New_Session_Yes_Click

End Sub

Yes this is the format i was looking for. But how do i get the proper session number to display. I got the new record to come up with the current date however the session number is giving me problems. the primary key in this is the session id. should i pass the session id value in order to display the session number? Session_Number_Textbox.Value = acLast +1 is giving me trouble. if so, how would i do this? here is the code:

Private Sub New_Session_Yes_Click()
> > On Error GoTo Err_New_Session_Yes_Click
> >
> > DoCmd.GoToRecord , , acNewRec
> > Session_Number_Textbox.Value = acLast + 1
> > Session_Date.Value = Now()
> >
> > Exit_New_Session_Yes_Click:
> > Exit Sub
> >
> > Err_New_Session_Yes_Click:
> > MsgBox Err.Description
> > Resume Exit_New_Session_Yes_Click
> >
> > End Sub

You say its giving you problems but you don't specify what kind of problem.

If you want the session id to be one greater than the last session id, is there a reason you're not making this an autonumber? Manually assigning this value can be problematic especially in a multi-user environment.

the value acLast + 1 (which i have set for the session_number_textbox.value) is not giving me the correct session number. For some reason, acLast + 1 = 4 which is the same for all clients (no matter how many sessions they have had). The value will is fixed and will not change accordingly to the number of sessions per client. session id is the unique identifier for each session where session number is the number of sessions each client has, so it is not linear like session id. many clients can have the same session number, but within each of their own attributes i need an automatic session number to be created. if the next session id in line is 55 and that particular client has already had 3 sessions, i would need 4 to come in the in the session number field. the session id is just to identify the sessions uniquely, but the session number tells me how many times each client has been counseled. is there a way to see what the last value is of the session number for each client and add 1 to that to be displayed when a new record is created? could this not be done in a simple one-line way as the date value is also determined? thanks.

ameen










privacy (GDPR)