Good day all,
Using Access 2007 but DB is created in Access 200o format so I can still use User Level security.
I have what am sure is a very newbie question. I want to add a control to a form which allows the user to type in a value, in this case a site #, and have the form navigate to the corresponding record.
I found some sample code that does this using a Combo Box. I tried modifying this code to use an unbound Text Box but I keep getting a runtime error 13 Type Mismatch Error.
Rec_Num is a Text Field.
Form is a Single Form.
Here is the code I am using: -
Private Sub txtFindSite_AfterUpdate()
-
-
Dim rs As Object
-
-
Set rs = Me.Recordset.Clone
-
rs.FindFirst "[Rec_Num] = " & Str(Nz(Me![txtFindSite], 0))
-
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
-
-
End Sub
If you guys need more info just let me know. Thanks in advance for any help.
Regards,
Garrett
10 10701 ADezii 8,834
Recognized Expert Expert
Good day all,
Using Access 2007 but DB is created in Access 200o format so I can still use User Level security.
I have what am sure is a very newbie question. I want to add a control to a form which allows the user to type in a value, in this case a site #, and have the form navigate to the corresponding record.
I found some sample code that does this using a Combo Box. I tried modifying this code to use an unbound Text Box but I keep getting a runtime error 13 Type Mismatch Error.
Rec_Num is a Text Field.
Form is a Single Form.
Here is the code I am using: -
Private Sub txtFindSite_AfterUpdate()
-
-
Dim rs As Object
-
-
Set rs = Me.Recordset.Clone
-
rs.FindFirst "[Rec_Num] = " & Str(Nz(Me![txtFindSite], 0))
-
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
-
-
End Sub
If you guys need more info just let me know. Thanks in advance for any help.
Regards,
Garrett
In the AfterUpdate() Event of txtFindSite: -
Private Sub txtFindSite_AfterUpdate()
-
Dim rs As DAO.Recordset
-
-
If Not IsNull(Me![txtFindSite]) Then
-
Set rs = Me.RecordsetClone
-
rs.FindFirst "[Rec_Num] = '" & Me![txtFindSite] & "'"
-
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
-
Else
-
Exit Sub
-
End If
-
End Sub
In the AfterUpdate() Event of txtFindSite: -
Private Sub txtFindSite_AfterUpdate()
-
Dim rs As DAO.Recordset
-
-
If Not IsNull(Me![txtFindSite]) Then
-
Set rs = Me.RecordsetClone
-
rs.FindFirst "[Rec_Num] = '" & Me![txtFindSite] & "'"
-
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
-
Else
-
Exit Sub
-
End If
-
End Sub
Thank you for the tip. This fixes the runtime error, but the form does not navigate to the Site # that's keyed into the text box.
This probably seems trivial. I'm only doing this due to user preference. Most of mine prefer to key in Sites from memory rather than scroll through the list in a Combo Box.
Does the Text Box need to be on a particular part of the Form? It is currently on the Form Header.
ADezii 8,834
Recognized Expert Expert
Thank you for the tip. This fixes the runtime error, but the form does not navigate to the Site # that's keyed into the text box.
This probably seems trivial. I'm only doing this due to user preference. Most of mine prefer to key in Sites from memory rather than scroll through the list in a Combo Box.
Does the Text Box need to be on a particular part of the Form? It is currently on the Form Header.
Does the Text Box need to be on a particular part of the Form?
No, it can be anywhere on the Form.
form does not navigate to the Site # that's keyed into the text box.
Which Field are you trying to Navigate to, [Rec_Num] as indicated in your code, or [Site #]?
No, it can be anywhere on the Form.
Which Field are you trying to Navigate to, [Rec_Num] as indicated in your code, or [Site #]?
Sorry for the confusion. Sometimes I'm dumber than I give myself credit. :)
Rec_Num is the Primary Key for the table on which my Form is based. I can see now that the Combo Box code I am modifying for use with the Text Box is finding the first Rec_Num in the table that is associated with whatever Site # is chosen in the Combo Box.
That explains why the Text Box code isn't working, because it's being given a Site # and trying to match it to a value in the Rec_Num field.
This presents me with a new issue. It is possible for a Site # to appear more than once in the table. Hence the use of Rec_Num as a Primary Key. Rec_Num is an Auto_Number field.
It seems as if, in order to look up a unique record via an unbound Text Box control, I may have to come up with a unique but intuitive way of identifying which instance of Site # I want to find. A Site may be visited several times, the difference being which project it is associated with at a given time.
So, maybe two unbound Text Boxes, one for Site # and one for Project name? Does that sound reasonable to the more seasoned folks around here?
Thanks again for any assistance. This is great site and has been a tremendous help.
Garrett
NeoPa 32,568
Recognized Expert Moderator MVP
If there are many jobs per site then that sounds good.
If it's rare (but not impossible) for a site to have multiple jobs then I'd consider keeping it as it is, but checking the table with the AfterUpdate code for multiples. If any exist, THEN pop up a form selecting the jobs from those that match the site.
If there are many jobs per site then that sounds good.
If it's rare (but not impossible) for a site to have multiple jobs then I'd consider keeping it as it is, but checking the table with the AfterUpdate code for multiples. If any exist, THEN pop up a form selecting the jobs from those that match the site.
Yeah, a Site may occur numerous times, but only once per Project, so a multi-box filter sounds like the way to go.
Out of curiosity, how would one go about determining if multiple records resulted from a filter, using VBA? I probably won't need to use it now, but I can envision other applications for that capability.
Thanks for the pointers. :)
Garrett
NeoPa 32,568
Recognized Expert Moderator MVP
...
Out of curiosity, how would one go about determining if multiple records resulted from a filter, using VBA? I probably won't need to use it now, but I can envision other applications for that capability.
...
You would use the Count() function within SQL or DCount() within VBA. There's also the facility to handle the On No Data event of a Report.
ADezii 8,834
Recognized Expert Expert
Sorry for the confusion. Sometimes I'm dumber than I give myself credit. :)
Rec_Num is the Primary Key for the table on which my Form is based. I can see now that the Combo Box code I am modifying for use with the Text Box is finding the first Rec_Num in the table that is associated with whatever Site # is chosen in the Combo Box.
That explains why the Text Box code isn't working, because it's being given a Site # and trying to match it to a value in the Rec_Num field.
This presents me with a new issue. It is possible for a Site # to appear more than once in the table. Hence the use of Rec_Num as a Primary Key. Rec_Num is an Auto_Number field.
It seems as if, in order to look up a unique record via an unbound Text Box control, I may have to come up with a unique but intuitive way of identifying which instance of Site # I want to find. A Site may be visited several times, the difference being which project it is associated with at a given time.
So, maybe two unbound Text Boxes, one for Site # and one for Project name? Does that sound reasonable to the more seasoned folks around here?
Thanks again for any assistance. This is great site and has been a tremendous help.
Garrett
So, maybe two unbound Text Boxes, one for Site # and one for Project name? Does that sound reasonable to the more seasoned folks around here?
What you are referring to is called a Composite Primary Key, and is the combination of 2 or more Fields, which defined together as a single Primary Key, will make each Record Unique. It is a perfectly acceptable practice, and if the combination of [Site #] and [Project Number] produce a Unique combination, they can be used as a Primary Key. Be advised that a search for a Unique Record may now involve multiple criteria ([Site #] and [Project Name]).
What you are referring to is called a Composite Primary Key, and is the combination of 2 or more Fields, which defined together as a single Primary Key, will make each Record Unique. It is a perfectly acceptable practice, and if the combination of [Site #] and [Project Number] produce a Unique combination, they can be used as a Primary Key. Be advised that a search for a Unique Record may now involve multiple criteria ([Site #] and [Project Name]).
I'm familiar with the concept of a Composite Primary Key. I was actually intending to set up my table that way. However, I was also looking for a way to track changes to records and came across some pre-made code by a gent named Allen Browne to create an Audit Trail. His code required that the table being tracked have an Auto Number field as the Primary Key, so I modified my table accordingly. I'm not yet savvy enough in VBA to modify his code to use my original Composite Key setup. I'm still struggling to find a way to modify his code to only track changes if certain fields on a Form are changed, as opposed to how it works now where a complete before and after copy of the record is recorded if anything on the form changes. If anyone has some good advice on that, I'd be forever grateful. :)
Regards,
Garrett
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: dmiller23462 |
last post by:
Hi guys....I have absolutely NO IDEA what I'm doing with Javascript
but my end result is I need two text boxes to stay hidden until a
particular option is selected....I've cobbled together the JavaScript
in this code from a couple different sites but I'm not 100% sure what
each line is doing...This is the ASP code that I'm using for the
page....Take a look at the JavaScript code and please let me know what
each line is doing....I have been...
|
by: Steven Stewart |
last post by:
Hi there,
I have posted about this before, but yet to arrive at a solution. I am
going to try to explain this better.
I have an Employees table and a Datarecords table (one-to-many
relationship). I have fields called InventoryOut and BalanceEnd that
are calculated on the fly for reports and when displayed on forms (they
are not part of any table). I have another field called "BalCarFor"
(Balance Carried Forward) that is part of the...
|
by: Carl |
last post by:
I have a main form with navigation buttons on it and a label showing
for example Record 1 of 15 using recordsetclone on it and eveything
works fine. When I move through the records the record number changes
fine. Within this main form I have a subform detailing distribution
records for the contact (main form is based on this) that also has
navigation buttons and a label showing Distribution 1 of ##. This is
where the problem lies. The...
|
by: Corepaul |
last post by:
As input is typed into a text box, I would like to search a recordset for the
first record that matches what has been typed so far. I would like to update
the text box to display the letters typed by the user in the normal font and
any additional characters in the first matching record displayed as highlighted
text. This matches the action of the MS Access help index.
I can use the OnKeyPress event to trigger a new check for the "best"...
|
by: M. David Johnson |
last post by:
I cannot get my OleDbDataAdapter to update my database
table from my local dataset table. The Knowledge Base
doesn't seem to help - see item 10 below.
I have a Microsoft Access 2000 database which indexes
computer magazine articles for personal reference.
I am developing a Visual Basic.NET program whose sole
purpose is to enter new records into the database. No
updates to existing entries, no deletions, and no display
| |
by: Bob Sanderson |
last post by:
I am trying to create a form for a MySQL database similar to a spreadsheet.
The idea is to display a list of records, with the last line of the list
being an input form. When the user enters data in the form and hits the
submit button, the data is entered and the form is reloaded with the new
data displayed and another input form becomes the last line.
Example ---
Before entering new data
|
by: beebelbrox |
last post by:
Hi, I am new VB programming in Access and I am requesting help with the following code.
WIndows OS MSaccess 2003
This code is attached to an unbound form that will display a specific recordset based in information passed to the form from another form. The problem I am having is that for each of the case statements the proper records (values and Number of records) are being returned but the movement commands do not work or do not display...
|
by: smorrison64 |
last post by:
I have a form that is coded to open a File Dialog boc to pick a picture to display on that particular item on a subform. My form is not based on query, but rather two separate tables (one primary, one sub). That code is working properly. How do I get that to translate to my report? I use VBA code because I use formats other than BMP for the pictures. Would basing the form on the qury that the report is based on solve the issue without...
|
by: angelicdevil |
last post by:
i have listbox 1 which displays status , based on selection of status listbox 2 displays usernames. and based on username selected the textbox displays the email id.
its working fine till displaying user names in listbox 2 based on selected of status in listbox. but its not displaying the emailid based on selection of usernames and when i click on search for users button its refreshs the page and shows the intial page also want the text box...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |