If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Runtime Error 2465
This code worked for the most part. I kept getting an error about 'can't add
or modify a record because a related record is needed in table ...' I think I figured it out... at least it's working now. I ended up having to move some code (what would have been the Else portion of the IF...Then) into other control events to get everything to dovetail. I really appreciate the help you guys gave me on this. I am sure I will be back with more questions as I move forward with this database... R Brown "Douglas J. Steele" wrote: Looking closer, you've got an extra double quote in there after the AND (which I happened to propagate). Try: IF Dcount("*", "q_OpSupSecurity", "UserID=" & txtUserId1 & _ " AND SecLevelOp3") = 0 THEN -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "iamrdbrown" wrote in message ... The expression(s) looks for the comma, not a semi-colon. It changed what gets high-lighted as the error point when I changed the comma to a semi-colon. With the original code, I get the error at the end like there is something missing - a list separator or such. My boss keeps talking about sending me to school for VB/VBA... I sure do wish he would... (smile). It would save you guys a bunch of headaches. R Brown "Douglas J. Steele" wrote: What are your Regional Settings? Might you have them set such that you need to use a semicolon rather than a comma as a list separator? See whether this works: IF Dcount("*";"q_OpSupSecurity";"UserID=" & txtUserId1 & _ " AND "SecLevelOp3") = 0 THEN -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "iamrdbrown" wrote in message ... John, I apologize for being dense, but when I plug this in I get a 'Expected: list separator or )' error. The code I plugged in is as follows: IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND "SecLevelOp3") = 0 THEN I am still very new to VB/VBA and have fits trying to debug... I have learned a LOT from you guys here, but still have a ways to go before I even consider myself 'literate' with this programming thing. R Brown "John Spencer" wrote: So you can expand the criteria to something like the following assuming that you have a field named Clearance and it is a number field. IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1 & " AND Clearance 3") = 0 THEN '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === iamrdbrown wrote: Actually, I am trying to confirm that the UserID is in the table with the correct clearance (3) before allowing the data being input into the textbox to be saved. I don't want just ID number to work - just the ones with the appropriate clearance. TIA, R Brown "John Spencer" wrote: You cannot use a query that way. Perhaps what you want is to use the DCount function to see if the id already exists in a table or a query. IF Dcount("*","q_OpSupSecurity","UserID=" & txtUserId1) = 0 THEN MsgBox "Invalid Operator ID. Please try it again.", _ vbExclamation, "Operator ID Entry" txtUserID1 = 0 txtUserID1.SetFocus End If John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County iamrdbrown wrote: I am trying to use a query (q_OpSupSecurity) with a 'IsNull' to limit input to a textbox. The query is based on a table where Operators =1 and Supervisors = 2. The query is acting as a filter to remove values that shouldn't be allowed into this textbox. I get a "Microsoft Office Access can't find the field '|' referred to in your expression" error when I try to run the code. How else can I accomplish what I am trying to do with this code? Private Sub txtUserID1_AfterUpdate() If IsNull([q_OpSupSecurity]) Then MsgBox "Invalid Operator ID. Please try it again.", vbExclamation, "Operator ID Entry""" txtUserID1 = 0 txtUserID1.SetFocus End If End Sub Thanks in advance, R Brown |
#12
|
|||
|
|||
Runtime Error 2465
You're setting txtOperator to a zero-length string (""). Presumably you've
got a relationship between whatever table you're updating and some other table on the Operator, so that a ZLS isn't valid for that field. Your original post shows that you're doing the validation in the AfterUpdate event. It strikes me that you should be doing it in the BeforeUpdate event, so that you don't save the record until the data's been fixed. Note that you might need to use the form's BeforeUpdate event, rather than the control's BeforeUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "iamrdbrown" wrote in message ... This did fix the problem with that. Now I have another problem that I think is linked to the Dcount... If I put in a value that returns a 0 from the Dcount, I get the following error: "you cannot add or change a record because a related record is required in table..." Here is the code for one section (I have 3) that is giving this problem. If DCount("*", "Users", "UserID=" & txtOperator & _ " AND SecLevel3") = 0 Then MsgBox "Invalid Operator ID. Please try it again.", vbExclamation, "Operator ID Entry""" txtOperator = "" txtOperator.SetFocus Else: txtDate1.Visible = True txtDate1 = Date txtSupervisor.Visible = True txtSupervisor.SetFocus End If I suspect it is tied to the table relationships, but I can't figure out why... R Brown "Douglas J. Steele" wrote: Looking closer, you've got an extra double quote in there after the AND (which I happened to propagate). Try: IF Dcount("*", "q_OpSupSecurity", "UserID=" & txtUserId1 & _ " AND SecLevelOp3") = 0 THEN |
|
Thread Tools | |
Display Modes | |
|
|