Display Record on Form based on value in Text box

[maxx429]

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

[ADezii]

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

[maxx429]

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]

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 #]?

[maxx429]

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]

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.

[maxx429]

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]

...
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]

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]).

[maxx429]

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

[ADezii]

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.