Results 1 to 12 of 12
  1. #1
    Join Date
    May 2014
    Posts
    98

    Unanswered: Popup shows up asking for ID

    I am getting 2 problems what I am trying to do is depending on the user loging in I want to open form (Aform) on specific record for that person ID. However where Y=1 I am getting a popup asking for AForm ID and where Y=2 I am getting run time error 3075 Syntax error (missing operator) in query expression.
    Code:
    Private Sub Command6_Click()
     
     If IsNull(txtUsername) Then
        MsgBox "Invalid username"
        Exit Sub
    End If
    If IsNull(txtPassword) Then
        MsgBox "Invalid password"
        Exit Sub
    End If
     
    Dim X As Long
    Dim SQL As String
    X = Nz(DLookup("UserID", "UserT", "Username='" & txtUsername & "' AND Password='" & txtPassword & "'"))
    'MsgBox "" & X & ""
    If X > 0 Then
        Dim Y As Long
        Y = Nz(DLookup("GroupID", "GroupXUsersT", "UserID='" & X & "'"))
        
        'MsgBox "" & Y & ""
        'We have a valid user
        If Y = 1 Then
            DoCmd.OpenForm "MainMenu"
            DoCmd.OpenForm "AForm", acNormal, , "[AID] = " & txtUsername       
            Forms!MainMenu!txtUserID = X
            Forms!MainMenu!txtUsername = txtUsername
            Forms!AForm!txtUserID = X
            Forms!AForm!txtUsername = txtUsername
            DoCmd.Close acForm, "LoginF"
        End If
       If Y = 2 Then
            DoCmd.OpenForm "MainMenu"
            DoCmd.OpenForm "NewHireFeedbackForm", acNormal, , "[NewHireID] = " & txtUsername
            Forms!MainMenu!txtUserID = X
            Forms!MainMenu!txtUsername = txtUsername
            Forms!NewHireFeedbackForm!txtUserID = X
            Forms!NewHireFeedbackForm!txtUsername = txtUsername
            DoCmd.Close acForm, "LoginF"
        End If
    Else
        MsgBox "Invalid Logon"
    End If
        
    End Sub
    Last edited by jj2; 03-31-15 at 10:23.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what datatype is X?
    what lines are throwing the error?
    what datatype is AID?
    what happens if you put a break point on the code and step through it to see what happens on each line of code?

    as said before
    when suppyling literal values in SQL
    numbers should not be delimited, but be an appropriate type. (ie if column is one of the integer types then there shoudl be no decimal), if it should have a decimal then supply one, even if its .0
    text MUST be delimited with either ' or "
    dates MUST be delimited with # and be the appropriate format
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2014
    Posts
    98
    Lines marked in red are throwing the error.
    DoCmd.OpenForm "AForm", acNormal, , "[AID] = " & txtUsername
    DoCmd.OpenForm "NewHireFeedbackForm", acNormal, , "[NewHireID] = " & txtUsername

    AID is an autonumber
    it gives out values fine if I break the code

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so when Y =1 and you get
    However where Y=1 I am getting a popup asking for AForm ID
    that would suggest that the form Aform doesn't have ID (or more likely as you are refrrrign to AID, doesnt' have AID defiend within it. so Access presuems AID is a parameter and requests you enter a value for that parameter
    where Y=2 I am getting run time error 3075 Syntax error (missing operator)
    thats impossibel to diagniose as we can't see the actual SQL, we don't knwo the data types of the supplied columns. your naming of columns and variable is incosistent. Id exepct a column or variable called ID to be numeric, ...name to be text/string

    it could be that you dont' have a control or variabel called txtUsername and so the SQL engine cannot supply a value. like I say in the absence of seeign what is beign sent to the SQL engine its virtually impossible to tell.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2014
    Posts
    98
    that would suggest that the form Aform doesn't have ID (or more likely as you are refrrrign to AID, doesnt' have AID defiend within it. so Access presuems AID is a parameter and requests you enter a value for that parameter
    form Aform does have AID text box. How should I define AID in the code

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't know
    ..because you report that:-
    where Y=1 I am getting a popup asking for AForm ID
    ...so if your form called 'Aform' does indeed have a control and or column called 'AID' then that would infer the error message is complaining about not being able to find a control or column called 'ID'
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2014
    Posts
    98
    So there is some confusion as I don't have any column named ID.
    So what I am trying to do is I have a login form with two unbound boxes username and password. and Atable with 2 columns as AID (autonumber) and Password. I have 2 type of users. 1 is general users and 2 is Admin. For the type of users I created a Group Table. There is userTable. Usertable has userID, Username and Password as coloumns. I have a conjuction table where I state which user belong to which group. What I want to do is based on type of users so if user 1 (userID1) login should open Aform which has two txtboxes AID and Password from Atable. to his specific record. SO if I login through login form, Aform should always open my record. I don't if that makes sense

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the confusions is:-
    you report the error message as
    where Y=1 I am getting a popup asking for AForm ID
    but your code refers to AID
    Code:
    DoCmd.OpenForm "AForm", acNormal, , "[AID] = " & txtUsername
    ...does your form open without specifying AID.
    eg:-
    Code:
     DoCmd.OpenForm "AForm", acNormal
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2014
    Posts
    98
    No I am using this
    DoCmd.OpenForm "AForm", acNormal, , "[AID] = " & txtUsername

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes, its clear from your code you are using:-
    Code:
    DoCmd.OpenForm "AForm", acNormal, , "[AID] = " & txtUsername
    but the error message you reported was
    However where Y=1 I am getting a popup asking for AForm ID
    so assumignyou have accurately reported that error message, somewhere in your form yoiu are referring to a column or control called 'ID'
    hence why Im asking does your form open (if you do not provide anything else. ie just opens from the navigation panel or code without specifying the AID. IE is this a fault with the form as (currently) designed / laid out, or is it a fault with the the way you are calling it via code
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    May 2014
    Posts
    98
    No my mistake. I meant code is breaking here.
    DoCmd.OpenForm "AForm", acNormal, , "[AID] = " & txtUsername

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes, so you keep saying.

    this discrepancy is between your code which attempts to set the value of a control or column in the form Afrom called AID to something AND the error message you report which refers to ID.
    the two are not the same

    ...unless its a typo and the error message referred to AID but you've put it here as ID
    So right now Im trying to get you to identify IF the form opens if you call it without specifying the AID value. Im trying to get you to diagnose if the error is caused by
    1) a design problem in the form (ie no matter how its opened there is an internal fault causing it to fail)
    OR
    2) if its a problem with the way you are opening the form using the docmd macro and the problem is how the value is passed to the form

    I am assuming for now that txtUsername has valid data in (presumably numeric)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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