By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

Display Record on Form based on value in Text box

P: 13
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtFindSite_AfterUpdate()
  2.  
  3. Dim rs As Object
  4.  
  5.     Set rs = Me.Recordset.Clone
  6.     rs.FindFirst "[Rec_Num] = " & Str(Nz(Me![txtFindSite], 0))
  7.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8.  
  9. End Sub
If you guys need more info just let me know. Thanks in advance for any help.

Regards,
Garrett
Feb 19 '08 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,705
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtFindSite_AfterUpdate()
  2.  
  3. Dim rs As Object
  4.  
  5.     Set rs = Me.Recordset.Clone
  6.     rs.FindFirst "[Rec_Num] = " & Str(Nz(Me![txtFindSite], 0))
  7.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8.  
  9. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtFindSite_AfterUpdate()
  2. Dim rs As DAO.Recordset
  3.  
  4. If Not IsNull(Me![txtFindSite]) Then
  5.   Set rs = Me.RecordsetClone
  6.   rs.FindFirst "[Rec_Num] = '" & Me![txtFindSite] & "'"
  7.   If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8. Else
  9.   Exit Sub
  10. End If
  11. End Sub
Feb 19 '08 #2

P: 13
In the AfterUpdate() Event of txtFindSite:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtFindSite_AfterUpdate()
  2. Dim rs As DAO.Recordset
  3.  
  4. If Not IsNull(Me![txtFindSite]) Then
  5.   Set rs = Me.RecordsetClone
  6.   rs.FindFirst "[Rec_Num] = '" & Me![txtFindSite] & "'"
  7.   If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  8. Else
  9.   Exit Sub
  10. End If
  11. 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.
Feb 19 '08 #3

ADezii
Expert 5K+
P: 8,705
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 #]?
Feb 20 '08 #4

P: 13
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
Feb 20 '08 #5

NeoPa
Expert Mod 15k+
P: 31,769
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.
Feb 20 '08 #6

P: 13
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
Feb 20 '08 #7

NeoPa
Expert Mod 15k+
P: 31,769
...
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.
Feb 20 '08 #8

ADezii
Expert 5K+
P: 8,705
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]).
Feb 21 '08 #9

P: 13
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
Feb 21 '08 #10

ADezii
Expert 5K+
P: 8,705
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
FYI, you can create a Unique Index on multiple Fields without it being the Primary Key, your Primary Key would be maintained independently of the Unique Index.
Feb 21 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.