Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Typamatic field (Access 2000, SP1)

    This one is probably simple, but I'm a newbie at Access; VB I can handle.

    I'm working on a form that has one field with a keypress event. I've got the code to handle watching the incoming data. What I would like to do is to create a field that will take the text on the field, and perform a simple query in a table "Offices", and query against the field "Office ID". It only needs to return the first match (which is, by the way, unique), and the associated fields in the record.

    If I can get over this hurdle, I'll be OK. Thanks!

    Brian

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Typamatic field (Access 2000, SP1)

    What does the KeyPress event do?
    Is the field you are entering the Office ID field?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Typamatic field (Access 2000, SP1)

    The keypress event is raised when a key is pressed in a field on a form. With it, I hope to be able to take the current value in the field, and perform a query of Office ID on the table, then display the results in the field on the form. It would in effect, be filling in the field as the user types (similar to the way Quicken fills in fields). It also forces the user to only enter valid data. The result is similar to populating a combo box with the allowable values, then let the user choose the data. Same effect, but the interface looks different.

    When the user tabs away from the field, the Field_Exit event is raised, and I will then populate the remaining parts of the form with the fields from the last query.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Typamatic field (Access 2000, SP1)

    Well, for a start I would use the BeforeUpdate event of the control to do the test if the Office ID field is valid, but that is up to you.
    I would use the following in the BeforeUpdate event (this event is called when a user has entered data into the control and has pressed Enter or Tab to move on to the next field):
    Dim dbs as DAO.Database, rs as DAO.Recordset
    Set dbs = CurrentDB
    Set rs = dbs.OpenRecordset("SELECT * FROM Offices WHERE [Office ID]=" & lngOfficeID)
    If rs.Eof then
    MsgBox "Invalid Office ID"
    Cancel = True
    Exit Sub
    End If
    Set rs = Nothing
    Set dbs = Nothing

    I have assumed that lngOfficeID is the Office ID field you are keying into and is a numeric field on the form.
    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Typamatic field (Access 2000, SP1)

    Well, I'm getting so close I can almost taste it. Here's the query I'm trying:

    Set rs = dbs.OpenRecordset("SELECT * FROM [Branch Office] WHERE [Office ID]=" & Branch_Office.Text)

    The Office ID is a string field.

    I added a reference to DAO, but I'm still getting an error - Runtime Error 3061 - too few parameters. Expected 1.

    I suspect that something hasn't been resolved. I'll keep digging. Thanks!

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Typamatic field (Access 2000, SP1)

    Your statement
    Set rs = dbs.OpenRecordset("SELECT * FROM [Branch Office] WHERE [Office ID]=" & Branch_Office.Text)
    should read:
    Set rs = dbs.OpenRecordset("SELECT * FROM [Branch Office] WHERE [Office ID]=" & Chr(34) & Branch_Office.Text & Chr(34))

    The reason this (surrounding the text with Chr(34))is done is because you have to enclose the text in single quotes.

    I would like to know Branch_Office.Text is, I would assume that it is the control on the form, it should be the control name.

    Too few parameters means that you have an undefined field in your query, are you sure that Office ID is the name of your field in the table?

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Typamatic field (Access 2000, SP1)

    Is there any reason you are not using a ComboBox?

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Typamatic field (Access 2000, SP1)

    Yes - design specifications passed down. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> It's one of those "We don't know how to use that" sort of things.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Typamatic field (Access 2000, SP1)

    From what you described in your first post you are trying to simulate a ComboBox anyway? Can't you explain that to them? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Or are they ..., I won't ask anymore about them.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Typamatic field (Access 2000, SP1)

    Let's just say - these people have been filling out forms by hand, and anything like a Combo box would probably scare them.

    By the way, that worked wonderfully. I'm on the way to completing this, and without bothering my digestion. Thank you very much!

    Brian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •