Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    41

    Unanswered: new to sql please help

    i use access 2000 as my front end. i had a form named main with a couple of unbound fields on it . i used to put in last name and first name and then in a query i would put "like [forms]![main]![text0] & "*"
    and also "like [forms]![main]![text2] & "*"

    the lookup patient button would open my patient form based on the query results of what i typed in on the main form . ex: i type in riley it would give me my patient form with all the riley's in it.

    how do i get my access form to open from information i am looking for in a query now that my whole database is back on a sql server.

    by the way i upsized to a adp file no longer using mdb. please if possible explain in detail how to do this as i am having trouble with the syntax of sql. i understand i cant use "*" as a wildcard now but % instead.

    thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hoe are you connecting to SQL Sever?

    You shouldn't have to change your code....did you create linked tables?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    41

    no linked tables

    im assuming they not linked tables since it is now a access project i dont see the little globe when i used to use link tables.

    i think everything is useing passthorugh queries now all my tables are on the sql server. just the forms and macros are on the frontend now.


    my code i had to change was something like

    dim db as dao.database
    dim rs as dao.recordset

    i had to change that to
    dim conn as adodb.connection
    dim rs as new adodb.recordset

    and quite a few other things

    if i left them linked the queries should have stayed working but im trying to get away from passing things through the jet engine and using the full power of sql .

  4. #4
    Join Date
    Sep 2003
    Posts
    41

    by the way

    this was my old code to make a chartnumber based on the first and last name fields. this is the one that didnt work .


    Public Function chartlookup()
    Dim db As DAO.Database <----believe to be the problem
    Dim rs As DAO.Recordset <----- belive to be the problem
    Dim SQL As String
    Dim NewNum As Integer
    Dim NeWChartNum As String

    SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

    Set db = CurrentDb() <----- -problem
    Set rs = db.OpenRecordset(SQL) <----problem

    If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400


    If rs.EOF = False Then
    If IsNull(rs!RecNum) = False Then
    NewNum = rs!RecNum + 1
    Else
    NewNum = 1
    End If
    End If
    'If NewNum = 1 Then

    [Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
    'End If

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well this is a TOTAL guess...

    If you're doing passthrus, you'll need SQL Server syntax..

    (Why not just call stored procedures if you're making such a radical change?)

    anyway...

    Code:
    SQL = "SELECT (MAX(CONVERT(int,(RIGHT(chartnumber))))
      FROM tblpatientinfo
     WHERE UPPER(LEFT(chartnumber,5)) = " _  
    	 & "'" UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

    my front end skills have gotten rusty....

    You might want to also look to post here:

    http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Posts
    41

    i got that code to work

    i did get that code to work but my main real problem is how do i get user sitting at my main form where he has to enter a patients lst name and first then when he hits the lookup patient button it opens my patient record with only the patient he typed in from the previous form . this was easy on access just added the expression into the query . but sql doesnt seem to support forms based queries. so what i really need is what does sql use instead of forms based queries to prompt a user for what they want to fill a record with.


    if i use select lname from tblpatientinfo where lname="riley"

    my form will only give me patients with last name riley


    but how do i get it to ask what name we are looking for i guess would be the real question here. do i make a stored procedure to prompt for the information and base the form on the stored procedure or does my from on open have to request what im looking for. i may not be useing the correct wording so please be patient but sql is a whole new world to me.
    i tried useing a filter on my form but if i put the stored procedure in the filter it doesnt recognize the stored procedure name only access query names it seems.

Posting Permissions

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