Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    items&group (2k)

    relation with items & groups in my examble
    ashrafk
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: items&group (2k)

    First, I would suggest that you change the name of the "group_name" field in your tblItems to "group_id". You can still set the caption of the field to "group_name", but you normally use field names that suggest the contents. Since you have a "group_name" field in tblGroup, it's confusing to see a totally different kind of field with the same name but actually containing a foreign key to the "group_id" field in tblGroup.

    As for your question, I don't understand it.
    <hr>I WANT WHEN I PUT ITEM_ID FROM 1 : 1999 THE GRUOP NAME IS (A ) OR ITEM FROM 2:2999 THE GROUP IS (B ).....<hr>
    If the first range is from 1 to 1999, the second range *can't* logically be from 2 to 2999 because all the numbers from 2 to 1999 would be in both ranges as you specified them.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: items&group (2k)

    first thank u dear 4 ur nots it importanat really .
    second i make a mistak in my quest. i mean my items consistes of group first group start from 1000 : 1999 not from (1) & second group from 2000:2999 i want when the user start to enter the items data when he press 1150 that mean he in group A & if he press 3124 that he in group C .
    u know what i want now.
    thanks advanced
    ashrafk

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: items&group (2k)

    Well, I know how I would write the code, but there seems to be another problem in the sample database since it give a peculiar error when the code is run. Let me play with it a bit and see what I can find.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: items&group (2k)

    Your form was corrupted, so I created a new one with the same controls except for the question and it works properly. I'm attaching a revised database with the new form in it. The code I put behind the AfterUpdate event of the item_id textbox is:

    <pre>Private Sub item_id_AfterUpdate()
    If Me.item_id <= 1999 And Me.item_id >= 1000 Then
    Me.group_name = 1
    ElseIf Me.item_id <= 2999 And Me.item_id >= 2000 Then
    Me.group_name = 2
    End If
    End Sub</pre>


    Note that you have to use the actual number of the group_id from tblGroup, not the text value you want to see in the combobox.
    Attached Files Attached Files
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: items&group (2k)

    Hi dear Charlotte ur code is work very good & it is usuful when i have a little group max 10 group but i have more than 100 group like ( spear parts ) so i can not write 100 line .
    thanks
    ashrafk

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

    Re: items&group (2k)

    Why not add a couple of fields to your tblGroup table, StartItemNo and EndItemNo which are the limits (eg. 1000 and 1999). Then when you enter an ID you just need a DLookup to find the GroupName you need.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: items&group (2k)

    thanks patt
    but can modifi it in my example . pls
    ashraf

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

    Re: items&group (2k)

    I have modified Charlotte's database for you.
    I introduced 2 fields in table tblGroup that are ItemIDStart and ItemIDEnd that hold the start and end ranges for each item_id.
    I replaced the code:
    If Me.item_id <= 1999 And Me.item_id >= 1000 Then
    Me.group_name = 1
    ElseIf Me.item_id <= 2999 And Me.item_id >= 2000 Then
    Me.group_name = 2
    End If

    with:

    Dim dbs As DAO.database, rs As DAO.Recordset
    Set dbs = CurrentDb
    Dim strSQL As String
    strSQL = "SELECT group_id FROM tblGroup WHERE " & Me!item_id & " between ItemIDStart and ItemIDEnd"
    Set rs = dbs.openrecordset(strSQL)
    If Not rs.EOF Then
    Me!group_name = rs!group_id
    End If
    Set rs = Nothing
    Set dbs = Nothing

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

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: items&group (2k)

    really dear patt u r very good man
    many thanks for u
    ashraf

Posting Permissions

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