Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2003
    Location
    Riverina, NSW, Australia
    Posts
    17

    Unanswered: forcing data to show in a text or combo box?

    Hey all,
    I've built a db for storing data about noxious weed property inspections.

    The collected data will be retreived for use in internal reports and inspection notices via mail merge to property owners.

    I enter data into an inspection FORM.

    When I enter the property ID No. eg; RPRO125 I want to force the owners ID eg; BRO 001 to come up in the Owners ID text box instead of having to manually enter it.

    How do I acheive this?

    Should I post more detail regarding tables etc?

    Geoff P

  2. #2
    Join Date
    Nov 2002
    Location
    Melbourne
    Posts
    92

    Re: forcing data to show in a text or combo box?

    Hi Geoff ,

    U might want to a weed inspection in Canberra. Theres heaps of weeds in Parliament.

    When u mean type it in manually, do u mean in the merge doc format. If this is the case, make sure that u have OwnerID fiels included in the query the runs the mail merg. On the report make sure the OwnerID control source is owner ID.

    If this doesnt help u, u may have to explain a bit further on how you information is pulled into the report or u might want to attach a copy of the DB for people on this have a go at helping u.

    BillJ
    Native Fish Aust Vic

    Originally posted by Geoff P
    Hey all,
    I've built a db for storing data about noxious weed property inspections.

    The collected data will be retreived for use in internal reports and inspection notices via mail merge to property owners.

    I enter data into an inspection FORM.

    When I enter the property ID No. eg; RPRO125 I want to force the owners ID eg; BRO 001 to come up in the Owners ID text box instead of having to manually enter it.

    How do I acheive this?

    Should I post more detail regarding tables etc?

    Geoff P
    Last edited by billjov; 07-27-03 at 19:46.

  3. #3
    Join Date
    Jul 2003
    Posts
    38
    First, i suppose you use combo boxes for displaying the ID's. Anything else wouldn't be user-friendly. The following code should work with textboxes, too, but don't claim about a slow form ...
    Set the ComboBoxes OnChange event to a procedure, that selects the value for the other box:

    (FIELD1 is the column in your table TABLEXY, where Combobox1 (CB1) gets its values)

    private sub CB1_Cchange
    dim rs as recordset

    set rs=currentdb.openrecordset ( "Select FIELD2 from TABLEXY where FIELD1 = '" & me.CB1.value & "';",dbopendynaset)

    CB2.value=rs.fields(FIELD2).value

    rs.close

    exit sub

    You have to check for errors, too.
    Also it is necessary, that both ID's are unique, but according to your posting i think they are.

  4. #4
    Join Date
    Jun 2003
    Location
    Riverina, NSW, Australia
    Posts
    17

    forcing data?

    Originally posted by SubHugo
    First, i suppose you use combo boxes for displaying the ID's. Anything else wouldn't be user-friendly. The following code should work with textboxes, too, but don't claim about a slow form ...
    Set the ComboBoxes OnChange event to a procedure, that selects the value for the other box:

    (FIELD1 is the column in your table TABLEXY, where Combobox1 (CB1) gets its values)

    private sub CB1_Cchange
    dim rs as recordset

    set rs=currentdb.openrecordset ( "Select FIELD2 from TABLEXY where FIELD1 = '" & me.CB1.value & "';",dbopendynaset)

    CB2.value=rs.fields(FIELD2).value

    rs.close

    exit sub

    You have to check for errors, too.
    Also it is necessary, that both ID's are unique, but according to your posting i think they are.
    Thanks SubHugo,
    I haven't tried it yet but it is precisely what I want to do.
    I find it very difficult to explain my problems on here in a way that is clear.

    Regards
    Geoff P

  5. #5
    Join Date
    Jun 2003
    Location
    Riverina, NSW, Australia
    Posts
    17

    Forcing data again?

    HeySubHugo,
    I had a go at your code but I didn't really know what I was doing. I have no idea of what the error messages mean. eg; "error, expecting list seperator or). Worked out that a list seperator is _ but no matter where I put it it didn't work.
    I opened one of the other proceedures that have happened by magic and tried to copy the spacings of characters where possible but I have had no real success. The proceedure that I am attempting remains in red colouring (I presume that red means invalid or error).

    Here are the fields, tables and combo box names. Maybe you can arrange them as they are supposed to be.

    I mistakenly gave you the wrong combo names on my original post. the correct names are as follows.

    Field 1 = Property Number
    Tablexy = Property
    CB1= Property ID
    Field 2 = Shire Map Number
    CB2 = Map Number

    I want the shire map number for a property to be forced into CB2 when I enter the property number into CB1.

    Hope I'm not being a nuisance (

    Geoff P
    Last edited by Geoff P; 07-29-03 at 03:52.

  6. #6
    Join Date
    Jul 2003
    Posts
    38
    hi geoff!

    There are more seperators. By the way, '_' means you continue in the next line and is NOT a seperator...
    I think, you simply forgot the [" ... "] around your fieldnames in the SQL string, e.g.:

    private sub Property_ID_AfterUpdate
    dim rs as recordset
    dim FIELD1,FIELD2 as string
    dim TABLEXY as string

    FIELD1 = "Property Number"
    FIELD2 = "Shire Map Number"
    TABLEXY = "Property"

    set rs=currentdb.openrecordset ( "Select [" & FIELD2 & "] from [" & TABLEXY & "] where [" & FIELD1 & "] = '" & me.CB1.value & "';",dbopendynaset)

    me.Property_ID.value=rs.fields(FIELD2).value

    rs.close

    exit sub

    Thought you are familiar with SQL, sorry, so i didn't put the whole phrase in my posting, 'cause brackets are a pain to type ;-)
    Hope this time it works...

  7. #7
    Join Date
    Jun 2003
    Location
    Riverina, NSW, Australia
    Posts
    17

    Forcing Data

    Thanks Bud,
    I'll give it a whirl a work tomorrow.

    Geoff P

  8. #8
    Join Date
    Jun 2003
    Location
    Riverina, NSW, Australia
    Posts
    17

    Re: Forcing Data

    Originally posted by Geoff P
    Thanks Bud,
    I'll give it a whirl a work tomorrow.

    Geoff P
    SubHugo,
    This is what I put in. Keep getting asked for seperators and getting syntax error messages in the third line. Tried all sorts of variations of .!"[( to no avail.
    Dumb huh!

    Private Sub Property_ID_AfterUpdate()
    Dim rs As Recordset
    dim ([Property Number],[Shire Map]) as String
    dim ["Property"]as String

    Set rs = CurrentDb.OpenRecordset("select["& Shire Map Number &"]from["& Property&"] where ["&property number&"] = '" & me.Property_ID.value & '";",dbopendynaset)



    End Sub

    Private Sub Property_ID_Change()

    End Sub

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Property&"]

    needs to be

    Property &"]

    in VB Variable& is a nasty method for declaring the data type of a variable

    same goes for

    ["&property number&"]

    needs to be

    ["& property number &"]

    you also might want to change your variable declarations, i've never seen this syntax before and i doubt vb can handle variable names including spaces and quotes
    dim ([Property Number],[Shire Map]) as String
    dim ["Property"]as String

    change them to
    dim PropertyNumber as string, ShireMap as String
    Dim Property as string

    if that doesn't fix it, let me know and i'll see if i can see anything else.
    Last edited by m.timoney; 08-11-03 at 07:58.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    good god, geoff i've just read what your trying to do, forget being in the wrong ball park your on the wrong planet

    your trying to write vb with sql syntax

    if your more comfortable in sql lets try another method


    make a query

    Select [owners ID]
    From [property]
    Where [property ID] = [forms]![<THE NAME OF YOUR FORM>]![<THE NAME OF THE OBJECT WITH WHERE YOU TYPE THE PROPERTY ID>];

    then create a combo box in the form, set it's row source to the above query

    now open the properties of the object where you type the property id and set it's on change event to code ie use the code builder

    within that subroutine
    type me.<COMBO BOX NAME>.requery

    see if that does the job, replace everything between <> with the correct data
    Last edited by m.timoney; 08-11-03 at 08:47.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Jun 2003
    Location
    Riverina, NSW, Australia
    Posts
    17

    forcing data

    hi m.
    Thanks for the information.
    I am only just beginning to learn SQL and had no idea of how to go about what I wanted to do.
    As for VBA? Wouldn't know what it looked like if it bit me.)

    I'll try what you have suggested.

    Geoff P

Posting Permissions

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