Help with IIF statement

I need help with an iff statement on my form.  Basically I have a new record 
when the "Insert" key is pressed.  But I want to stay with the current 
record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours. 
Otherwise a new record is called for.  I think I'm close here but a little 
help would be appreciated.  Thanks, Randy

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrRoutine

Select Case KeyCode
Case vbKeyInsert
If [TotalHrs] > 0 Then
    If IDRb_subform.Form!SumHours <> [TotalHrs] Then
        If MsgBox("Total Hours for Applicants to not agree with Total 
Hours", vbYes + vbDefaultButton2) <> vbYes Then
        Me.Form![TotalHrs].SetFocus

'Case Else
'End Select

Else
        End If
        End If
        End If

Select Case KeyCode
Case vbKeyInsert
    DoCmd.GoToRecord , , acNewRec
Case Else
End Select
ErrRoutine:
    If Err.Number = 2105 Then
    Resume Next
End If
End Select
End Sub 


0
Randy
8/9/2007 6:53:19 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
540 Views

Similar Articles

[PageSpeed] 58

On Aug 9, 8:53 pm, <Randy> wrote:
> I need help with an iff statement on my form.  Basically I have a new record
> when the "Insert" key is pressed.  But I want to stay with the current
> record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours.
> Otherwise a new record is called for.  I think I'm close here but a little
> help would be appreciated.  Thanks, Randy
>
> Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
> On Error GoTo ErrRoutine
>
> Select Case KeyCode
> Case vbKeyInsert
> If [TotalHrs] > 0 Then
>     If IDRb_subform.Form!SumHours <> [TotalHrs] Then
>         If MsgBox("Total Hours for Applicants to not agree with Total
> Hours", vbYes + vbDefaultButton2) <> vbYes Then
>         Me.Form![TotalHrs].SetFocus
>
> 'Case Else
> 'End Select
>
> Else
>         End If
>         End If
>         End If
>
> Select Case KeyCode
> Case vbKeyInsert
>     DoCmd.GoToRecord , , acNewRec
> Case Else
> End Select
> ErrRoutine:
>     If Err.Number = 2105 Then
>     Resume Next
> End If
> End Select
> End Sub


Hi Randy,

I think you need to check if the record is dirty and  If [TotalHrs] >
0

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.Dirty and IDRb_subform.Form!SumHours > 0 Then
    Cancel = True
    MsgBox "Total Hours for Applicants to not agree with Total Hours",
_
       vbOKOnly, "Cannot Update"
  End If
End Sub

In case you add record in subform only, then you can use something
similar on BeforeUpdate of subform.

Regards,
Branislav Mihaljev

0
banem2
8/9/2007 7:31:10 PM
welllll....not entirely clear what you mean by an Insert 'key'....  button?

Guessing that you have a button that, via wizard, is set up to go to next 
record/new record....

So to wrap an if/then around this button, to make it conditional, then you 
have to select that button's OnClick property and open the VB code for that 
button.  ... look for the code line with DoCmd.FindNext (or New depending on 
which it is set up to do...)

in the line above this command put:
If Me.[TotalHrs] = Me![IDRb_subform].Form!SumHours Then

and in the line below the DoCmd put:
End If

This essentially makes the change record command conditional to the values 
you define.

hope this helps
-- 
NTC


"Randy" wrote:

> I need help with an iff statement on my form.  Basically I have a new record 
> when the "Insert" key is pressed.  But I want to stay with the current 
> record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours. 
> Otherwise a new record is called for.  I think I'm close here but a little 
> help would be appreciated.  Thanks, Randy
> 
> Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
> On Error GoTo ErrRoutine
> 
> Select Case KeyCode
> Case vbKeyInsert
> If [TotalHrs] > 0 Then
>     If IDRb_subform.Form!SumHours <> [TotalHrs] Then
>         If MsgBox("Total Hours for Applicants to not agree with Total 
> Hours", vbYes + vbDefaultButton2) <> vbYes Then
>         Me.Form![TotalHrs].SetFocus
> 
> 'Case Else
> 'End Select
> 
> Else
>         End If
>         End If
>         End If
> 
> Select Case KeyCode
> Case vbKeyInsert
>     DoCmd.GoToRecord , , acNewRec
> Case Else
> End Select
> ErrRoutine:
>     If Err.Number = 2105 Then
>     Resume Next
> End If
> End Select
> End Sub 
> 
> 
> 
0
Utf
8/9/2007 7:44:11 PM
The Insert Key is not a button, it is the actual "Insert" key on the 
keyboard.  Hence: Case vbKeyInsert.   When I press this key a new record 
appears.

> welllll....not entirely clear what you mean by an Insert 'key'.... 
> button?
>
> Guessing that you have a button that, via wizard, is set up to go to next
> record/new record....
>
> So to wrap an if/then around this button, to make it conditional, then you
> have to select that button's OnClick property and open the VB code for 
> that
> button.  ... look for the code line with DoCmd.FindNext (or New depending 
> on
> which it is set up to do...)
>
> in the line above this command put:
> If Me.[TotalHrs] = Me![IDRb_subform].Form!SumHours Then
>
> and in the line below the DoCmd put:
> End If
>
> This essentially makes the change record command conditional to the values
> you define.
>
> hope this helps
> -- 
> NTC
>
>
> "Randy" wrote:
>
>> I need help with an iff statement on my form.  Basically I have a new 
>> record
>> when the "Insert" key is pressed.  But I want to stay with the current
>> record if [TotalHrs] is greater or less than IDRb_subform.Form!SumHours.
>> Otherwise a new record is called for.  I think I'm close here but a 
>> little
>> help would be appreciated.  Thanks, Randy
>>
>> Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
>> On Error GoTo ErrRoutine
>>
>> Select Case KeyCode
>> Case vbKeyInsert
>> If [TotalHrs] > 0 Then
>>     If IDRb_subform.Form!SumHours <> [TotalHrs] Then
>>         If MsgBox("Total Hours for Applicants to not agree with Total
>> Hours", vbYes + vbDefaultButton2) <> vbYes Then
>>         Me.Form![TotalHrs].SetFocus
>>
>> 'Case Else
>> 'End Select
>>
>> Else
>>         End If
>>         End If
>>         End If
>>
>> Select Case KeyCode
>> Case vbKeyInsert
>>     DoCmd.GoToRecord , , acNewRec
>> Case Else
>> End Select
>> ErrRoutine:
>>     If Err.Number = 2105 Then
>>     Resume Next
>> End If
>> End Select
>> End Sub
>>
>>
>> 


0
Randy
8/9/2007 9:38:48 PM
Reply:

Similar Artilces:

Help Troubleshooting Before Update event
I've run into a problem getting a Before Update event to launch. I'm trying to update the LastUpdate field with the current date and time when data on a form is changed. The control is on a subform, which is also located on a tab control. I've checked the code on a "plain" form so I know it works, but I'm missing something when trying to make it work on the subform. Form Name: frmVisits Subform Name: frmVisitOrgTab Tab Name: pgVisitInfo Control Name: LastUpdate (formatted as General Date) Current Code: Private Sub Form_BeforeUpdate(Cancel As Integer) Me![frmVi...

Will Access Help me?
I am not familiar with Access and I am not sure if this is what I need. I get various spreadsheets from multiple clients with variable information in many different formats. I want to extract only the information I need into one common format that I can easlily use. I currently take their spreadsheets, copy, paste, delete and move around in excel and sort it to get the information I need. It takes tons of time only to find out the client sent a new spreadsheet with changes and I need to start all over again. Would Access be a good solution to my spreadsheet woes? Thanks for your i...

Disaster Recovery Disaster happening now need help
Exchange 2003 SP1. Total failure and rebuild same hardware. Using Veritas 10.0 Intelligent Disaster Recovery, got files and system state up thru last night's differential. Now trying Info Store restore using Veritas Exchange Agent and last night's backup. First attempt failed with event 457 and 904 log mismatch on Exchange server even though Veritas server reported restore successful. Info store was running, btw. Think may have been way I set up BackupExec job restore properties. Veritas says no, it's not related, but... Anyway, found kb 823016 and sort of followed it. Inste...

Publisher 2003 Question. This is a HARD one, hope you can help
In Publisher 2002, I can cut content from a page, open another document and paste it and it will paste in EXACLY on this page where I copied from the other. For example, I have a square in the upper right corner of a Publisher 2002 document. I copy it and open another Publisher 2002 document. When I select paste, it puts it in the upper right corner just like the original document. It didn't matter where my cursor was when I pasted. It came right in to that spot. But now that I've upgraded to 2003, when I copy and paste in, it seems to paste wherever my cursor happens to be at ...

How do you install help for excel 4.0 macros in Office 2003?
The download center says to download Excel 2000 Help File: Running Excel 4.0 Macros, but when I try to runit it says that no Windows 2000 installation is detected (logical, since I'm running 2003). The run then terminates. I guess they still haven't fixed this bug in the installer package. The help file for XL2000 that I have works OK with XL2002. I don't know about 2003, but if you post your email address, I can sent it to you. When you get it, search for the existing (stub) file of the same name (I think it's XLMACRO.CHM) and replace that file with the full file. On Wed...

need help with an if statement
right now i am using this code on a command button; Sub Save_As_FileName() FName1 = Range("d3").Value FName2 = Range("d5").Value Fname3 = Range("d6").Value Fname4 = Range("d7").Value pth = "f:\bids\" MyFileName = FName1 & " " & FName2 & " " & Fname3 & " " & Fname & " " & ".xls" ActiveWorkbook.SaveAs Filename:=pth & MyFileName End Sub But the pth will change, depending on what is in cell d2. so, I think i will need an If statement saying if cell d2 = ...

Help with Report
I'm trying to create a report based on a query. The data source (query and subsequent table) has about 15 columns and as many records. I need to create a report that lists each record's primary key followed by the results of any record that's <> null. E.g., (primary key) Blue Red Yellow (primary key) Red (primary key) Orange Green (primary key) Pink Blue Black I could easily do this with the report wizard or auto report; problem is, however, that null records show up. I could omit the null records, but then I'll have spaces in between records. Thoughts? alex ...

Help, Quicken convert -trouble with Online Transactions or Setup
System = Money 2003, Window 98SE, Logging in with Passport.net The Story: When I installed, Setup, Migrated my Quicken file (which all went well) The installation asked me to choose my financial institutions (which I did) it saw all my bank accounts and enabled them for online access. The Problem is: Each accout has a different user name and password (and it has to be that way because of the way my bank does it). So when I set it up, I used the User/Password for the Account I use most frequently. Now when I "connect to bank" using that accout the call summary says "success...

email help 04-04-10
help ...

Code help needed for Option Buttons to control Subs
I have a User form with (20) option buttons on it. It does what I want by only being able to check 1 option button. Option Button Names: OB_601 OB_602 - OB_619 OB_620 What I need help with is when user checks the correct option button, that button exicutes code located in a module. Code in Module: There are (20) Subs named like below Sub String_01() Sub String_02() - Sub String_19() Sub String_20() I think the code for each Option button is something like this, because there default position is False and they only show true when selected. If OB_601 = True the...

regex help
In regular expression how do I say characters A-Z except I and O? I'd rather not list each of the 24 valid characters. Thanks, LJB Regular Expressions is a great tool that just can't be told it MUST be learned. The following website is a great place to start. http://www.regular-expressions.info/reference.html I will give you a hint, you will need to make use of the caret (^). LJB wrote: > In regular expression how do I say characters A-Z except I and O? I'd > rather not list each of the 24 valid characters. [A-HJ-NP-Z] Lists of Matching Charac...

Function help???
Ok. I have three columns of text I'm dealing with here. The first column is a the "Athletes Name" column, which represents the athletes name, the second column is "M or F" which represents Male or Female, and the last column is "Team Name", which represents the team each person is play on. I have to give an analysis based on how many all male teams, female teams and co-ed teams we currently have competing based on the information provided. Is there a function on excel that can pull the teams that only have male athletes, female athletes and teams compri...

Help with face tagging
Not sure whether I understand what this is *supposed* to do, or whether I am expecting too much. Can somebody offer any insight... 1. My guess is that WLPG created rectangles around faces for about 10-20% of the photos in which faces occur. In no cases did it actually apply names to those faces. I can understand this happening for a new installation, when there were no names defined, but is that the way it is supposed to operate over the long term? In other words, does WLPG have the capability of distinguishing one face from anther and then applying the correct tag? I have seen contr...

HELP!!!! priv1.edb file corrupt
my exchange 2003 server will not allow me to mount the store, i get "internal error, restart imformation store service and retry" I put exchange onto a different server and populated the new machine with the users mailboxes, i attempted to copy the priv1.edb file into the new server, but users don't have access to the messages within that database, users can send and receive messages but all folders, messages, contacts etc from the old server are not accessable and the mailbox store shows 2 accounts for each user, one account with the new mailbox info and one with the o...

Domestic expenses in Excel, please help
Hello, I am not an excel developer, not even a user. A friend of mine suggested to track my daily expenes with Excel:- a) Category of expense (weekly food shoping, car expense, clothing ...) b) Price c) Date of purchase ..etc... At the end of the months I can then see how much money has been spent in each category and better plan my expenses. Does anybody of you has such an Excel sheet(s) that I could use already made? Many thanks! There are tons of free templates at: http://office.microsoft.com/en-us/default.aspx Maybe you'll find something you like. Jerminia wrote: > > H...

Looking for Macros Help
Anybody know anyone in the Phoenix, Arizona area that can do some Macro work for me? I've reached my level of incompetence and need some pai help. Thank you in advance, Dan Willis EP -- dwilli ----------------------------------------------------------------------- dwillis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2881 View this thread: http://www.excelforum.com/showthread.php?threadid=48502 Hi Dan, See: http://www.rentacoder.com/RentACoder/default.asp --- Regards, Norman "dwillis" <dwillis.1yif7a_1132010400.8106@excelforum-nos...

Need help with a formula.
I'm looking for a formula that will do the following... If the conditional formating in C2(=All_World_Activity>=All_World_Bonus*5) is true it will display the contents of F4(which can also be a cell named All_World_Total). If the conditional formating in C2(=All_World_Activity>=All_World_Bonus*5) is false it will see if F4 (or All_World_Total) - Trans_type!F6 (or All_World_Bonus) is greater than F4 (or All_World_Total). If it is it will display =F4-Trans_type!F6 (or =All_World_Total-All_World_Bonus). If the above F4 (or All_World_Total) - Trans_type!F6 (or All_World_Bonus) is <...

Help with referenced sheet
Hi all, I am attempting to feed data into a cell on sheet ABC that comes from another sheet that will be created programmatically (call it sheet XYZ). I start by creating the reference with sheet XYZ already existing, so that I can pick the cells I want off of it. I then delete sheet XYZ. My cells on ABC all then become #REF, which is okay with me. I save and close. I then run the program that takes some data, opens my excel file, and (re)creates sheet XYZ and puts the data I want onto it. The problem is that all of my cells on ABC that reference XYZ keep the #REF until I double ...

Need Winsock help
Hope someone can help. I have an application that uses the winsock control to get the HTML source from a web page. This Page contains some info that I need which i place into a TextBox. I then parse a link from that page and use this link to get source for 2nd page. On the 2nd Page is a Link to a Picture and I use this to download and save an image. At the same time I have 2 more URL's based on the first getting more info ie fullsummary and synop What seems to happen is 80% of the time all works OK, picture downloads etc the other 20% the second page of source code only ...

Help And Printing
I have two problems. 1. I would go to the help file, but when I do, none of the links work. The table of contents area is shaded. What can I do to make this work? 2. How can I choose a print area and then CENTER it on the page I am printing? Thanks in advance. Candy Answered my own question: Found it in an old post from two years ago. Someone was missing their hhctrl.ocx file. Well, I had mine, right where the old post said it would be – in c:\windows\system32. If you had to add the file from your Windows CD, you would then want to make sure that windows sees it—you would have t...

Purchase Order Receipt Help
We are using GP 10.0 There is a purchase order that I am trying to enter a receipt for. It has multiple lines and equals 47,000. I have not received all the items but I must pay a 22,000 deposit that is not a line item on this PO. How would I do this? How do I adjust it so that I can pay this amount and still know that I would later owe the vendor 25,000. Please Help!! Thanks To repeat Richard Whaley's saying "You don't pay POs, you pay invoices.". With that said, if you need to put a deposit down on an order, you will need to create a payment by issuing a ch...

Help with placing Picture in background of a chart
Here is my code ActiveChart.PlotArea.Select Selection.Fill.UserPicture PictureFile:="C:\My Documents\Picture2.jpg" Is there any way to program a picture into a chart that is in a worksheet in the current workbook instead of having to get it from a file. ...

Relationship mapping help (CRM 4.0)
Hi, I've created a new field in Lead and Account sections. The idea is that the user creates a lead and fills in this text field (nvarchar, length 25). I've edited the relationship mapping from Lead to Account so the related field in Account is filled in when the Lead is converted to an Account. I also want the field editable in the Account form so that it can be altered later on. I've created 4 new fields in both Lead and Account (all are simple text fields), and 3 of them work but the 4th mapping doesn't. I get the error message asking me to check a list of things but...

Installed 2 LiteOn SATA drives - Win7 64bit does not recognize
After my IDE drives were not seen by Win7 64bit, I bought 2 LiteOn Sata drives OEM from Newegg. I have SATA cables, and power cable to attach 2 SATA cables from the drives. Attached both cables to the power cable (which is split - one power plug and hookup for the 2 SATA cables. Installed and still Windows Explorer will not see them. I've looked in my BIOS and have 3 options for the OnChip SATA device: IDE RAID AHCI I have it set to IDE, no good. I've tried AHCI and no good. I am not rigged for RAID and don't want it. Does anyone have any idea of what's w...

If statement depending on NAME of workbook
I would like certain events to occur depending on what the name of the workbook is. Is there a way to do this? What would be the code for this? Can something like if WorkbookName = "Name" Then Or am I totally out of whack here? Rob rob, You can use either ThisWorkbook.Name or ActiveWorkbook.Name e.g. If ThisWorkbook.Name = "Test.xls" Then ' do something Else ' do something else End If John "rob nobel" <robnobel@dodo.com.au> wrote in message news:eygDEsaqDHA.688@TK2MSFTNGP10.phx.gbl... > I would like certain events to occur depending on wh...