items&group (2k)

[ashrafk]

relation with items & groups in my examble
ashrafk

[charlotte]

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.

[ashrafk]

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

[charlotte]

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]

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.

[ashrafk]

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

[patt]

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>

[ashrafk]

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

[patt]

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>

[ashrafk]

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