VBA for NotInList return to old record or BeforeUpdate value

I have a unbound combo box to find a record on a form. it is basically wizard 
created code. if the ID is NotInList the form jumps to a random record and 
the combo still displays the Invalid ID giving the impression the correct 
record is selected. i tried to add a message box which pops up but the form 
maintains the same behavior. After the message box is confirmed i would like 
everything to remain on whatever record was previously selected before the 
Invalid ID was entered

Private Sub Form_Current()
Me.SelectByPN_Combo = Me.TRKID 'Updates SelectByPN_Combo when navigation 
buttons are used
Me.SelectBySN_Combo = Me.TRKID
Me.SelectByTRKID_Combo = Me.TRKID
Me.SelectByDescription_Combo = Me.TRKID
End Sub

Public Sub SelectByTRKID_Combo_AfterUpdate()
' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[TRKID] = " & Str(Nz(Me![SelectByTRKID_Combo], 0))
If rs.NoMatch = True Then
    MsgBox "The selected ID does not exist!"
    Else
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

Sorry i Pester you guys so much
Thanks

Barry
0
Utf
11/20/2009 6:04:02 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
1212 Views

Similar Articles

[PageSpeed] 46

Barry A&P wrote:
>I have a unbound combo box to find a record on a form. it is basically wizard 
>created code. if the ID is NotInList the form jumps to a random record and 
>the combo still displays the Invalid ID giving the impression the correct 
>record is selected. i tried to add a message box which pops up but the form 
>maintains the same behavior. After the message box is confirmed i would like 
>everything to remain on whatever record was previously selected before the 
>Invalid ID was entered
>
>Public Sub SelectByTRKID_Combo_AfterUpdate()
>' Find the record that matches the control.
>    Dim rs As Object
>    Set rs = Me.Recordset.Clone
>    rs.FindFirst "[TRKID] = " & Str(Nz(Me![SelectByTRKID_Combo], 0))
>If rs.NoMatch = True Then
>    MsgBox "The selected ID does not exist!"
>    Else
>If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>End If
>Set rs = Nothing
>End Sub


You are setting the form's bookmark under the wrong
condition, probably because the stupid wizard did it that
way.

Try something more like:

Public Sub SelectByTRKID_Combo_AfterUpdate()
' Find the record that matches the control.
	With Me.RecordsetClone
		.FindFirst "[TRKID] = " &
Str(Nz(Me![SelectByTRKID_Combo], 0))
		If .NoMatch = True Then
    		MsgBox "The selected ID does not exist!"
		Else
			Me.Bookmark = .Bookmark
		End If
	End With
End Sub

Post back if the item is always in the combo box list or if
users can type whatever they want in the combo box.  In it
is always in the list, the code could be different. If hte
only way to get NoMatch is when a user enters junk into the
combo box, you could just set the combo box's LimitToList
property and let Access deal with situation.

-- 
Marsh
MVP [MS Access]
0
Marshall
11/21/2009 12:25:20 AM
Thank you for the code marshall i will play with it a little to see if i can 
get whatever complicated thing i was trying to do..  But for now "limit to 
list" i feel so stupid for overlooking the obvious..  Thanks again

Barry

"Marshall Barton" wrote:

> Barry A&P wrote:
> >I have a unbound combo box to find a record on a form. it is basically wizard 
> >created code. if the ID is NotInList the form jumps to a random record and 
> >the combo still displays the Invalid ID giving the impression the correct 
> >record is selected. i tried to add a message box which pops up but the form 
> >maintains the same behavior. After the message box is confirmed i would like 
> >everything to remain on whatever record was previously selected before the 
> >Invalid ID was entered
> >
> >Public Sub SelectByTRKID_Combo_AfterUpdate()
> >' Find the record that matches the control.
> >    Dim rs As Object
> >    Set rs = Me.Recordset.Clone
> >    rs.FindFirst "[TRKID] = " & Str(Nz(Me![SelectByTRKID_Combo], 0))
> >If rs.NoMatch = True Then
> >    MsgBox "The selected ID does not exist!"
> >    Else
> >If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> >End If
> >Set rs = Nothing
> >End Sub
> 
> 
> You are setting the form's bookmark under the wrong
> condition, probably because the stupid wizard did it that
> way.
> 
> Try something more like:
> 
> Public Sub SelectByTRKID_Combo_AfterUpdate()
> ' Find the record that matches the control.
> 	With Me.RecordsetClone
> 		.FindFirst "[TRKID] = " &
> Str(Nz(Me![SelectByTRKID_Combo], 0))
> 		If .NoMatch = True Then
>     		MsgBox "The selected ID does not exist!"
> 		Else
> 			Me.Bookmark = .Bookmark
> 		End If
> 	End With
> End Sub
> 
> Post back if the item is always in the combo box list or if
> users can type whatever they want in the combo box.  In it
> is always in the list, the code could be different. If hte
> only way to get NoMatch is when a user enters junk into the
> combo box, you could just set the combo box's LimitToList
> property and let Access deal with situation.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 
0
Utf
11/24/2009 7:18:01 AM
Reply:

Similar Artilces:

source of a record in entire database(s)
Mornin' everyone, I have an EmailAddress record that I need to know the source of. Basically we have about 10 databases with 100 tables/views. I need to know exactly where a particular EmailAdress is loacted thru out the entire database. Is there a quick and eifficient way of performing this? Let me know what you think. Thanks and have a good day! Himansu search web for db object quickfind, which is an SSMS add-in that will do what you need. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Himansu" &l...

How to search a specific string value in Entire registry....
Hi Could anybody please tell me about how to search a specific string in Entire registry, any code snippet or hint about this problem? Thanks in advance... Mujtaba Did you not try using RegEnumKeyEx/RegEnumValue? Do this on all hives and you should be OK. What specific problem did you have with it? -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Mujtaba" <tomujtaba@hotmail.com> wrote in message news:8f4f92aa.0501080242.44b0dd36@posting.google.com... > Hi > > Could anybody please tell me about how to search a specific string in > Entire registry, any code snip...

Record audio from microphone?
How do I add a feature to my Windows MFC program that allows me to record microphone audio to a wave (or MP3) file? I have only been using MFC so I have no idea where to start with this kind of advanced functionality? Do I need to download some sort of library? Hi Si! > How do I add a feature to my Windows MFC program that allows me to record > microphone > audio to a wave (or MP3) file? waveInOpen -- Greetings Jochen My blog about Win32 and .NET http://blog.kalmbachnet.de/ ...

Wrong URL returned by GetCrmServerUrl after server move
We have moved our CRM 4.0 front-end from oldserver to newserver The GetCrmServerUrl function called by AuthenticationPipeline still returns oldserver Why? CRM 4 has been uninstalled, no trace of oldserver in config files, registry or database. CRM Deployment shows only new server Meanwhile, both C360 applications and Stunnware Tools crash in trying to connect to oldserver with No connection could be made because the target machine actively refused it oldserver_ip Where this this metadata stored to keep reference on server removed from deployment ? Thanks for your help ...

Multiply all values by 10
Is there a way to multiply all values in my spreadsheet by 10? If so, how? Type the 10 in some random cell and copy. Select your target cells and Edit > Paste Special, and select the Multiply radio button. "RTimberlake" wrote: > Is there a way to multiply all values in my spreadsheet by 10? If so, how? First, make a copy of the workbook in case you make a typo or something, then put 10 in an empty cell, copy it, then select all values needed and do edit>paste special and select multiply -- Regards, Peo Sjoblom (No private emails please) "RTimberlake" ...

Office 2004, where's the Value Pack?
I run Mac OS X v10.3.5 and Microsoft Office 2004 Student and Teacher edition. Where's the value pack? I can't find it on the CD, and I did a full install of Office. I'm missing a lot of office assistants, templates (i.e. Resumes in the Project Gallery, etc.) What happened? Norm Phillips <norm@alnora.com> wrote: > I run Mac OS X v10.3.5 and Microsoft Office 2004 Student and Teacher > edition. Where's the value pack? I can't find it on the CD, and I did a full > install of Office. I'm missing a lot of office assistants, templates (i.e. > Resumes in t...

vba GOAL SEEK
Hi, I would like to make a command.button that runs the goal.seek formula for me. I have a cell that has the total value for a series of equasions. lets call it "TOTAL" and it usualy is a number like 543.23 Some times i need it to round it to 543, but not aways. and i cant just round it. the value must come out from somewere. there is were the goal.seek come in. so, the goal.seek formula in VBA is goalseek(Goal,ChangingCell As Range)As Boolean and my curent formula for the button is Private Sub CommandButton1_Click() Sheets("ADITIVO").Select Range("E57")....

Cannot Delete Any Records
We're running CRM 3 Rollup v3 and we have recently found that we cannot delete any records in CRM. We recently migrated over to a Windows 2003 Enterprise install running SQL 2000 Enterprise. Everything else is working without issue for us. For example I am a Sys Admin, and I receive a generic SQL Server error when going to delete an activity? I know that in the past this was possible, any thoughts? Sean; sounds like something went wrong with your migration. you can run a CRM trace or a SQL Profiler trace while performing the deletion to see if you can pin-point the error. Dave Ire...

Publisher skipping every other record -- how to stop that
I've used Publisher for 2 years now, same template, and today, I had 293 records to print and it skipped every other record. Is there a way to tell Pubshlier to print even/odd pages rather than deleting each record that printed each time I reattach the file. This has taken all day, and I had 38 left - deleted the rest, reattached the new file, and it skipped every other one. HELP! Susan I stand corrected. It prints the first half every other record, and then prints the others after the first batch prints. They are all at the back of this last print job. I have never seen Pub...

Removing various combinations of text and returning only a number in excel
A very good day to all, I am looking for a formula that would remove all non-numeric data from cells and return only a number. PS- notice that there is one numeric character that also needs to be removed - the number after the "m". also note that the formula must be able to handle each variation of text below. Data: A1 = "Gaz = 3 421 786 m3" A2 = "Gaz=3 421 786 m4" A3 = "Gaz=3421786m5" A4 = "Gaz = 3,421,786 m6" A5 = "Gaz=3,421,786m7" A6 = "Gaz =3 421 786 m3" Result: The resultant for each line should be "...

Reusing Union Query with different variable values
I have a union query that appends fields from several tables based on a given criteria, e.g., id like *30060. Example: Select [tblJAN].field1, [tblJAN].field2, [tblJAN].field3, [tblJAN].id From [tblJAN] HAVING [tblJAN].id LIKE "*30060" UNION SELCECT [tblFEB].field1, [tblFEB].field2, [tblFEB].field3, [tblFEB].id From [tblFEB] HAVING [tblFEB].id LIKE "*30060" The query actually appends more fields from about 12 or more monthly tables based on the same criteria, e.g., id like *30060. I would like to be able to change the value of the id variable on the fly in order to r...

Record Copy
I want to move any file from one database to another that is older than one year. What is the syntax for this? OldSQL = "INSERT INTO C:\History\Old.Mdb\tblChecksTMP " & _ "SELECT * FROM tblChecks " & _ "WHERE tblChecks.BizDay = Date()-365;" DoCmd.RunSQL (OldSQL) NOOldSQL = "DELETE * FROM tblChecks " _ & "WHERE tblChecks.BizDay = Date()-365;" DoCmd.RunSQL (NOOldSQL) -365 days doesn't seem right? Also I'm moving it to another database. Thanks DS OldSQL = "INSERT INTO C:\History\Old.Mdb\tblChecksTMP " & _ ...

Searching a Row of Data and Returning a Value
I have tried all sorts (less macros which I am seeking to avoid) and searched the web but to no avail!! I am trying to use Excel to search a row of data that is interspersed (i.e. not in a continuous range but instead for example B3, W3, AB3 etc) and may or may not be populated with text values. Having searched the row I want to be able to select the latest text value (i.e. the row represents a timeline of data). See example below: A3 W3 AB3 Jim "" (i.e. blank returned by formula) John Therefore I can se...

Copying Data Without Hidden Values
Please excuse a dumb question. I'm sure the answer is buried in the help files somewhere, but I can't find it. I'm running Excel2002. I have a long column of data with many hidden rows. I need to copy this column for a paste operation, but I want to include only the active, visible fields. Every copy process I've tried ends up pasting the hidden rows also. How do I exclude the hidden rows from the copy process? Thanks, OC Oscar, Select the column, then hit F5, click on Special, select Visible Cells Only, OK, then Copy and Paste. HTH, Nikos "Oscar" <oc@sc.rrno...

Lookup Value in Regarding field
I have written a script in the phone call onSave that fills in the Sender and Recipient fields base on the value of the Owner and Regarding. My problem is that there are 4 possible option values for Sender/Recipient and 11 values from Regarding. Example: Company, User, Contact, Lead are in all of them. If I choose Opportunity in Regarding, it errors out because the typename does not match any typenames in Sender/Recipient. What is the scriupt necessary to check for this before performing the action? Thanks. -- Client Solutions Manager Micro Strategies ...

Pasting values not formulas in Excel 2002
How does one copy and paste values rather than formulas in Excel 2002? I have Excel 97 and Excel 2000 and in both one can do this through Edit -> Paste Special But Paste Special does not give you this option in Excel 2002 and I cannot find any help in the online Help with about how to paste values. Hi Stephen, I use XL2002 and I can Copy --> Paste Special --> Values with n problem. Edit Paste Special Values Values is in the Paste Section in "Paste Special" and is the thir option on the left. I hope this helps you. Carlos Lopez -- Message posted from http://ww...

Can't add new record to form
I have 2 tables that are linked. One table holds the Main Data - a collection of cost totals, the second holds additional data about one of the items (a breakdown of one of the cost totals) from the Main Data. I created the main form, then I created the second form and used a command button to open the second form. On historical data this worked perfect. Now I tried to add a new record starting with the main form. When I hit the command button to open the linked form, the form would not open. After that, I then realized that I can't even open the second form by itself and ad...

Query Wizard not returning data
I created a Query in Query Wizard to pull data from a SQL database and return the data to and Excel spreadsheet. I had been using this Excel spreadsheet regularly without hitch or glitch for 7 months until I updated from SQL 7.0 to SQL 2000, and something went terribly wrong In the Excel spreadsheet, when I right click refresh data, the data does not update. When I go into the Query Wizard, the query will update but will not return to the Excel spreadsheet. I recreated the entire query from scratch in a new workbook and still had the same problems. I have a boat load of RAM and have chec...

Report Parameters forms value list set by VBA?
NOTE: I"m a VBA novice I have a set of reports that use a common Report Parameter form. When I first designed them it was convenient as the same set of filters were applicable to all the reports: Example: report for invoices, report for open orders, report for processing orders Filters were: Location, Manager & Employee Now they also want to filter by date range, which won't consistent between reports: IE. Invoices = 30, 60 or 90 days old vs Pending Orders = 10, 15, 30 days old I'd like to just add one more drop down box to the parameter report and have the value list ch...

Put a query value in a textbox controlsource
Hey everyone, I want to put a value from a query into the control source property of a text box in a form. The value is "fldToday" and is a count of today's work orders counted in query named "qryToday". The form already has a record source assigned to another query and the field is in the contol source of another text box. The help says I can use a value in a query but can't seem to get the right string to get it to work . The query works when I run it and returns the correct values. Thanks ahead of time !! Dan S -- Danny C. Sperry Message posted via Acc...

Use values instead of labels on X-axis in Area/Stacked Area Charts
When building a stacked area chart, independent of how the linked cells are formatted, the chart uses the x-axis values as simple text labels. Since my values are not equally spaced, this distorts the plot, and I have not found any method through the help menus to change this formatting. Any help would be greatly appreciated. This technique might give you some ideas: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=508 - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Ryan" <Ryan@discussions.micr...

Run query from VBA
I have a query in my DB that returns 1 value. I would like to run it in VBA and assign it to a variable. I thought this would be easy but cannot seem to figure out how. Can it be done without a full recordset? Example: HV_QUERY = (results from query) Thanks in advance. There are other methods but a simple work-around would be to create a form based on the query and place it in your main form as a subform (don't link with master and child). Requery the subform and use Me.somecontrol = Forms!MainFormName!SubFormName.Form!ControlOnSubForm You can set the visible to no if you want for...

Excel2007: How to change line color from gradient to solid via VBA?
Hi, is there any possibility in Excel2007 to change a line color from gradient line color to solid line color or vice versa using VBA? I have created a line with a gradient color from yellow to blue, this line should turn into solid grey when the corresponding cell value is zero. When using a solid line color in the first place there is no problem with shape.line.forecolor.schemecolor=55, however, when using a gradient color this command doesn't work, the line color remains gradient. I haven't found any info in the Excel help, is there any solution to this? Thanks in advance for ...

Default values?
I have the attached spreadsheet that I want to make all of the fields i Yellow default to zero each time the user opens the sheet. In addition I want to make each of the drop down boxes default to the answer NONE Does anyone have suggestions? I thought If I saved the sheet as template that would do it, but I have not had luck with that +---------------------------------------------------------------- | Attachment filename: for_posting.xls |Download attachment: http://www.excelforum.com/attachment.php?postid=366452 +-------------------------------------...

Error in VBA connection
hi, i make Recordset with connection to SQL server in VBA and there is error number " -2147467259" raised with desc "Unspecified error" and i don't know how to solve it.also i install SP3 and no use please help thank you ...