Increment Text box value from combo box selection

[foxtet]

Hi every one

I have table A patients and table B doctors. when patient come for consultation, i want issue token number for each patient from a doctor they wish... i tried hours and hours on this task but still i found NO solution..can any expert in this forum kindly help me how to get a SOLUTION for this....



any help is appreciated???
fox

[khalid]

Hi Fox,
Welcome to AFNet.

You want to issue the token for each patient consulting each doctor on certain visit date?

Your combination would be:

TokenNo|DoctorName|PatientName|DateVisit
Token001|Doctor01|Patient01|28-May-11
Token002|Doctor01|Patient02|28-May-11
Token003|Doctor01|Patient03|28-May-11

Token001|Doctor02|Patient01|28-May-11
Token002|Doctor02|Patient02|28-May-11
Token003|Doctor02|Patient03|28-May-11

Token001|Doctor03|Patient01|28-May-11

This would you mean???

[foxtet]

Thank you for the reply,
that's right. if it works that pattern than that's fine..

user can select doctor's name from combo box so the text box should display the next token for the selected doctor..

fox

[khalid]

Thank you for the reply,
that's right. if it works that pattern than that's fine..

user can select doctor's name from combo box so the text box should display the next token for the selected doctor..

fox

Okay,
Here is a code which issue tokens as per your request.

Private Sub Command4_Click()

'count the tokens...
Dim lngToken As Long
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "SELECT Count([tbltoken].tokenNumber) AS CountOfToken"
strSQL = strSQL + " FROM tbltoken"
strSQL = strSQL + " WHERE tbltoken.doctorid = " & Me.Combo0
strSQL = strSQL + " AND tbltoken.datetoken = " & Date

Set rs = db.OpenRecordset(strSQL)
lngToken = rs!CountOfToken

' lngToken = DCount("tokenNumber", "tbltoken", "doctorid =" & Me.Combo0 & " AND datetoken =" & Date) > 0
    
    If lngToken > 0 Then
    lngToken = lngToken + 1
    Else: lngToken = 1
    End If

Me.txtToken.Value = "Token No. " & lngToken

'record the token in the table with date
CurrentDb.Execute " INSERT INTO tbltoken " _
                  & "(tokenNumber, doctorid, datetoken) Values " _
                  & "('" & lngToken & "'," & "'" & Combo0 & "'," & Date & ")"
End Sub

two Tables involves:

tbldoctor (table)
with the following fields

ID (pirmery key)
doctorid
doctorname

tbltoken (table)
with the following fields
doctorid
tokenNumber
datetoken
==================
What this code does?

open the recordset and count the tokens for any doctor on the same dates.

'count the tokens...
Dim lngToken As Long
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
strSQL = "SELECT Count([tbltoken].tokenNumber) AS CountOfToken"
strSQL = strSQL + " FROM tbltoken"
strSQL = strSQL + " WHERE tbltoken.doctorid = " & Me.Combo0
strSQL = strSQL + " AND tbltoken.datetoken = " & Date

Set rs = db.OpenRecordset(strSQL)
lngToken = rs!CountOfToken

check for token if issued? and increment them:

If lngToken > 0 Then
lngToken = lngToken + 1
Else: lngToken = 1
End If

at last: record the issued token in the table

'record the token in the table with date
CurrentDb.Execute " INSERT INTO tbltoken " _
& "(tokenNumber, doctorid, datetoken) Values " _
& "('" & lngToken & "'," & "'" & Combo0 & "'," & Date & ")"
End Sub

Hope this meet your requirements:

the sample database can be attached if needed.

[foxtet]

Thank you for the timely reply.. the solution works, but I get error msg when clicked to issue memo button.

Im using ms access 2003, the OS is Windows 7

Pls find the attacted copy of mdb to work out the errors given below.

open the file---click frmMain--double click name from the listBox in the Main Menu---frmPatientInfo opens. click generate memedata button--service obtained window opens---when you click issue token the following error msg displays

Runtime error 3601
too few parameters. Expected 1.

the another issue is that Patient's age is not displaying on the service window, but I have made the default value to =[Forms]![frmPatientInfo]![txtAge]


I here by attch a copy of .mdb file.
Thank you for your kind attention

fox

[June7]

Assuming datetoken field is a Date/Time datatype, use # delimiters for the Date value:
CurrentDb.Execute "INSERT INTO tbltoken " _
& "(tokenNumber, doctorid, datetoken) Values " _
& "('" & lngToken & "', '" & Combo0 & "', #" & Date & "#)"

[foxtet]

Thank you for the reply
I copy pasted the code given by you. still the same error msg.

fox

[khalid]

Hi fox,
I have installed Access 2010 with window 7 operating system on my machine, your database needs to be converted to Access 2010 .accdb format.

If I do so it will be useless for your to use it on Access-2003, however I found some bugs on your form:

The combo0 is bound to column 2 on the form, it should be bound to column 1. (the id no)
Also you need to complile your database with:
1) Debug>>>Compile in the VBA mood and see where is the compilation errors:
2) change the Msgbox Error$ to MsgBox Err.Description
3) Add:
Dim strSQL as String on the declraration section.

'count the tokens...
Dim lngToken As Long
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL as string

Apply Break points on the code and watch the code execution by pressing F8 key while highligted in yellow.

[foxtet]

Hi Khalid
thank you for the help..


changing the bound column1 corrected the error msg. thank you for help

the other issue with token number is that with date ....

Token date recorded in tbltoken as follows
30/12/1898
I want token number date to be same as the system date on which the token is issued. it can be very helpful if tbltoken stores time as well

Thank you

[khalid]

Hi Khalid
thank you for the help..


changing the bound column1 corrected the error msg. thank you for help

Glad that it worked for you and you traced it out. Are you ready to mark this thread as solved? (Thread Tools>>>Mark as Solved) on top of this thread.

the other issue with token number is that with date ....

Token date recorded in tbltoken as follows
30/12/1898
I want token number date to be same as the system date on which the token is issued.

change CurrentDb.Execute " INSERT INTO tbltoken to:

CurrentDb.Execute " INSERT INTO tbltoken " _
                  & "(tokenNumber, doctorid) Values " _
                  & "('" & lngToken & "'," & "'" & Combo0.Value & "')"

and make the default value of datetoken to Date() in the table design (Medium Date format) so date will be recorded automatically in dd-mm-yyyy.

it can be very helpful if tbltoken stores time as well

Thank you

for time stamp: create a new column as tokenTime with Date/Time data type and change its default value to Now()


Hope this will solve the problem.