Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2003
    Posts
    41

    Unhappy Unanswered: primary key made from info in entered in fields

    here is my problem. i need to make a primary key based on info entered by users and some automatically by code. This is a medical database. When a user enters the patients first and last name i would like the primary key field called"chartnumber" to be filled in with the first three letters of the lastname "lname" and the first three letters of the first name "fname" then followed by a 0001. Then i need it to be 0002 if that record already exists and so on and so on. i have figured out how to get the first 3 of both fileds useing the left([lname],3) & left([fname],3) expression but the problem is the 0001 after it and the check to see if that number is already taken to make it 0002. please help. also if there is a way to do this in sql ill gladly put the database on sql backend and use access as the front end.

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: primary key made from info in entered in fields

    Originally posted by opcbriley
    here is my problem. i need to make a primary key based on info entered by users and some automatically by code. This is a medical database. When a user enters the patients first and last name i would like the primary key field called"chartnumber" to be filled in with the first three letters of the lastname "lname" and the first three letters of the first name "fname" then followed by a 0001. Then i need it to be 0002 if that record already exists and so on and so on. i have figured out how to get the first 3 of both fileds useing the left([lname],3) & left([fname],3) expression but the problem is the 0001 after it and the check to see if that number is already taken to make it 0002. please help. also if there is a way to do this in sql ill gladly put the database on sql backend and use access as the front end.
    Assuming you know how to manipulate recordsets....The query would be:

    dim RS as recordset
    dim SQL as string
    dim NewNum as Integer
    dim NeWChartNum as string

    SQL = Select Max(Cint(Right(chartnumber,4))) As RecNum
    From ChartTable
    Where UCase(Left(chartnumber,6)) = Ucase(left([lname],3) & left([fname],3))

    set RS=Currendb().Openrecordset(SQL)

    If RS.eof = false then
    if isnull(RS!RecNum) = false then
    NewNum = RS!RecNum+1
    else
    NewNum = 1
    endif
    endif
    rs.close
    NewChartNum =Ucase(left([lname],3) & left([fname],3)) & format(NewNum,"0000")


    Let me know if that is in the ballpark.
    Thanks
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    Sep 2003
    Posts
    41
    that is exactly what i need, believe it or not i understand the code you just gave me but being new to the access ,sql evironment where would i put it on the form where they are entering this data. do i make a command button to run the code or use an event in one of the fields.

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by opcbriley
    that is exactly what i need, believe it or not i understand the code you just gave me but being new to the access ,sql evironment where would i put it on the form where they are entering this data. do i make a command button to run the code or use an event in one of the fields.
    Best bet is to make it a lookup button on one form, that then opens another form to enter the data. The second form can also be populated with the prior chart data, so if it is arecurring complaint all the data entry person would have to do is update and then on click on the second form populates under the new record number.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    Sep 2003
    Posts
    41
    thank you for your help im working on it right now. ill post agian if i have any problems. forgot to mention this is a live database but im trying to enhance it they have multiple entries cause they use the test number for the primary key so if the patient has 5 tests they have to enter five times. im trying to make a chartid then have it linked to a test table so there is only one patient entrie with multiple test entries. once agian thank you very much.

  6. #6
    Join Date
    Sep 2003
    Posts
    41
    looks like im gonna have to stay with access do you have a code similar to the one you gave me in an access format im assuming that only works on sql since the code errors out and i get put to the debug screen

  7. #7
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by opcbriley
    looks like im gonna have to stay with access do you have a code similar to the one you gave me in an access format im assuming that only works on sql since the code errors out and i get put to the debug screen
    That was access code. I take it the data is SQL server.

    If so, just create an ODBC call to the SQL server. Then link the tables into Access. The Access will read/write to the tables as native.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  8. #8
    Join Date
    Sep 2003
    Posts
    41
    ok im sorry i though it was sql code because the code doesnt work for me it debugs first at .
    dim RS as recordset
    dim SQL as string
    dim NewNum as Integer
    dim NeWChartNum as string

    SQL = Select Max(Cint(Right(chartnumber,4))) As RecNum

    From ChartTable
    Where UCase(Left(chartnumber,6)) = Ucase(left([lname],3) & left([fname],3))

    ^^^^this line errors first at the select statement only way i can get it to stop is to put select from and where statement all on one line in quotes.


    set RS=Currendb().Openrecordset(SQL)
    ^^^^^^this line errrors

    If RS.eof = false then
    if isnull(RS!RecNum) = false then
    NewNum = RS!RecNum+1
    ^^^^theses lines red out im assuming because the previous line fails.
    else
    NewNum = 1
    endif
    endif
    rs.close
    NewChartNum =Ucase(left([lname],3) & left([fname],3)) & format(NewNum,"0000")

  9. #9
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Forgot to set the SQL statement up with line continuations. I do my SQL statements with line continuations to make them easier to read. This is what it should have been. This is the equivalent of making the SQL statement as one line.

    SQL = "Select Max(Cint(Right(chartnumber,4))) As RecNum " & _
    "From ChartTable " & _
    "Where UCase(Left(chartnumber,6)) = " & Ucase(left([lname],3) & left([fname],3))"

    The reason that the set RS=Currendb().Openrecordset(SQL) errors is because of the bad SQL statement. And everthing follows from there.

    Try it this way.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  10. #10
    Join Date
    Sep 2003
    Posts
    41
    that worked thank you so much , i much appreciate your time and effort.

  11. #11
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by opcbriley
    that worked thank you so much , i much appreciate your time and effort.
    Your welcome.

    Have fun with it. Otherwise you'll go insane!
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  12. #12
    Join Date
    Sep 2003
    Posts
    41
    i spoke to soon look like it was debugging find but putting that code behind a lookup button like suggested fails on line set rs= currentdb().openrecordset(sql) with error code run-tim error '3061' too few parematers. expected 1. error here is the code with my values replaced.

    Dim RS As Recordset
    Dim SQL As String
    Dim NewNum As Integer
    Dim NeWChartNum As String


    SQL = "Select Max(Cint(Right([chartnumber],4))) As RecNum " & _
    "From patient " & _
    "Where UCase(Left([chartnumber],6)) =" & UCase(Left([lname], 3) & Left([fname], 3))



    Set RS = CurrentDb().Openrecordset(SQL)<-----this line fails with that error

    If RS.EOF = False Then
    If IsNull(RS!RecNum) = False Then
    NewNum = RS!RecNum + 1
    Else
    NewNum = 1
    End If
    End If
    RS.Close
    NeWChartNum = UCase(Left([lname], 3) & Left([fname], 3)) & Format(NewNum, "0000")

  13. #13
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    My bad again. I keep forgetting you're almost a newbie at doing VBA code. And this gets a little more convoluted, but we are getting into the ballpark quickly.

    SQL = "Select Max(Cint(Right([chartnumber],4))) As RecNum " & _
    "From patient " & _
    "Where UCase(Left([chartnumber],6)) = '" & UCase(Left([lname], 3) & Left([fname], 3)) & "'"


    I didn't put in the single quote marks that are needed when you are combing strings into a SQL statement like this.

    Now the next question/assumptions:
    I assume you have the clerk filling out a basic form that is using lookup tables and combo boxes from the Patient table. Then after he finds/enters the basic patient info (name/address etc) he then clicks a button to start filling out the test info. Correct? Then on the click you are going to search the ChartTable to create the actual chart info? That is where the code comes in.

    So what you need to do is on the click bring up chart info is to set this equal to UCase(Left([lname], 3) & Left([fname], 3)) portion equal to the form field such as UCase(Left(me.<LastNameTextBox>.value,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) and then do add the record. This modifies both the SQL query and the fields you need to in the recordset and the variables.


    dim RS as recordset
    dim SQL as string
    dim NewNum as Integer
    dim NeWChartNum as string
    'here you add the field names from the table as _
    some variable id such _
    dim VarTestName as string _
    dim VarTestDate as Date
    , etc. _

    SQL = "Select Max(Cint(Right([chartnumber],4))) As RecNum, <FieldName1>, <FieldName2>, <FieldName3>, ..... " & _
    "From ChartTable " & _
    "WHERE UCase(Left([chartnumber],6)) = '" & UCase(Left(me.<LastNameTextBox>.value,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) & "'"
    set RS=Currendb().Openrecordset(SQL)

    If RS.eof = false then
    if isnull(RS!RecNum) = false then
    NewNum = RS!RecNum+1
    else
    NewNum = 1
    endif
    endif
    if NewNum= 1 then
    with rs
    .addnew
    !chartnumber=UCase(Left(me.<LastNameTextBox>.value ,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) & format(NewNum,"0000")
    .update
    end with
    else
    VarTestName=RS!TestName
    VarTestDate=RS!TestDate
    'do all the fields. You need to because as soon as you do the _
    .addnew function all the current records fields are set to _
    null/empty strings. This is making the assumption that _
    you want to take the data in the last record and put it into _
    the new record.
    With RS
    .addnew
    !chartnumber=UCase(Left(me.<LastNameTextBox>.value ,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) & format(NewNum,"0000")
    !TestName=VarTestName
    !TestDate=VarTestDate
    !......=Var........
    .update
    end with
    rs.close

    NewChartNum =UCase(Left(me.<LastNameTextBox>.value,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) & format(NewNum,"0000")

    Docmd.openform "ChartForm",acNormal ,, "chartnumber= '" & NewChartNum & "'",acFormEdit,acWindowNormal



    Try this on, and see if it makes any sense.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  14. #14
    Join Date
    Sep 2003
    Posts
    41
    ok almost there i think i did have to change a few things to stop that type mismatch error though the single qoutes didnt stop it.
    here are some changes but now with new error


    dim db as dao.database
    dim RS as dao.recordset
    dim SQL as string
    dim NewNum as Integer
    dim NeWChartNum as string
    'here you add the field names from the table as _
    some variable id such _
    dim VarTestName as string _
    dim VarTestDate as Date, etc. _

    SQL = "Select Max(Cint(Right([chartnumber],4))) As RecNum, <FieldName1>, <FieldName2>, <FieldName3>, ..... " & _
    "From patient " & _
    "WHERE UCase(Left([chartnumber],6)) = '" & UCase(Left(me.<LastNameTextBox>.value,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) & "'"
    set db=currentdb()
    set RS=db.Openrecordset(SQL)

    If RS.eof = false then
    if isnull(RS!RecNum) = false then
    NewNum = RS!RecNum+1
    else
    NewNum = 1
    endif
    endif
    if NewNum= 1 then
    with rs
    .addnew
    !chartnumber=UCase(Left(me.<LastNameTextBox>.value ,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) & format(NewNum,"0000")
    .update
    end with
    else
    VarTestName=RS!TestName
    VarTestDate=RS!TestDate
    'do all the fields. You need to because as soon as you do the _
    .addnew function all the current records fields are set to _
    null/empty strings. This is making the assumption that _
    you want to take the data in the last record and put it into _
    the new record.
    With RS
    .addnew <<-----THIS IS WHERE I ERROR WITH 3027 DATABASE READ ONLY
    !chartnumber=UCase(Left(me.<LastNameTextBox>.value ,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) & format(NewNum,"0000")
    !TestName=VarTestName
    !TestDate=VarTestDate
    !......=Var........
    .update
    end with
    rs.close

    NewChartNum =UCase(Left(me.<LastNameTextBox>.value,3)) & UCase(Left(me.<FirstNameTextBox>.value,3)) & format(NewNum,"0000")

    Docmd.openform "ChartForm",acNormal ,, "chartnumber= '" & NewChartNum & "'",acFormEdit,acWindowNormal


    "by the way the other thing i had to add was a reference to dao objects in the vb editor i had to put a check mark in the box. "
    all the other mismatch errors have stop when i dim db as dao.database

    set rs = db.openrecordset(sql)

    im thinking for this error 3027 i need to include some kind open [lockedit] in the recordset am i on the right path

  15. #15
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by opcbriley

    With RS
    .addnew <<-----THIS IS WHERE I ERROR WITH 3027 DATABASE READ ONLY

    "by the way the other thing i had to add was a reference to dao objects in the vb editor i had to put a check mark in the box. "
    all the other mismatch errors have stop when i dim db as dao.database

    set rs = db.openrecordset(sql)

    im thinking for this error 3027 i need to include some kind open [lockedit] in the recordset am i on the right path
    You're on the path. Is the charttable open by Form A? Is Form B open when you get this error? Are you opening the chart TABLE not a Query with other linked tables?

    Force form b closed
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •