me.orderby with refrence ID

[pyromaniac511]

I'm using

DLookUp("Model_Name","tblModels","Model_ID = " & [Model_ID])

in my data field of a textbox. I have also made a button for each of the columns to sort by.

Private Sub btnFilterModel_Click()
    If Me.OrderBy = "[Model_ID]" Then
    Me.OrderBy = "[Model_ID] DESC"
    Else
    Me.OrderBy = "[Model_ID]"
    Me.OrderByOn = True
    End If
End Sub

Now, obviously it filters based on the ID number. Is it possible for access to look at what the value the ID represents and sort from the word instead of the number?

[ssanfu]

You are talking about sorting, but the button name is "btnFilterModel".

If you are asking about sorting a on a text column, the answer is Yes.

Code would looks something like:

    If Me.OrderBy = "[Model_Name]" Then
        Me.OrderBy = "[Model_Name] DESC"
    Else
        Me.OrderBy = "[Model_Name]"
    End If

    Me.OrderByOn = True

[pyromaniac511]

its a button that filters the model column... I guess it make sense to me but I know I do suck at naming. When I use [Model_Name] it pops up asking for me to input one. My assumption is that it is because it is not part of my inventory table.

tblInventory
- Model_ID

tblModel
- Model_ID
- Model_Name

I think what I am trying to do is take the FK and dig down into tblModel and pull the make out of it.

[ssanfu]

Obviously the "Model_Name" field needs to be in the form record source to be displayed/sorted.

tblInventory
-ModelID ->tblModel -> ModelID

I have no idea what this means.

Post more info about the form, form record source, relationships. Or post your dB.

[orange]

I agree with Steve -- what are you dealing with. Until readers understand you and the issue before you, they can not offer focused advice.
So, as we have said in other posts before, pretend your in a line up at a fast food place and the guy behind you -who doesn't know Access or database -strikes up a conversation and you tell him about your business and your proposed database. What would say to describe your situation and the issue before you in as simple terms as possible.

[pyromaniac511]

Sorry for delay, I got busy at work. Here is the file.

inventory.zip

tblInventory
-ModelID ->tblModel -> ModelID

Was supposed to mean

tblInventory
- Model_ID

tblModel
- Model_ID
- Model_Name

I accidently submitted the page after deciding the original made no sense and was trying to change it (I pressed tab then enter like you would in a text editor). I had a couple of days separated from this so I am trying to remember where I am.

[orange]

A Brand may have Many Models
A Model may be represented by Many Items
An Item may exist in Many Locations

[pyromaniac511]

The item can only exist in one location. I may have 2 Cisco 2800 routers but SN 123 is in building 1 and SN 456 is in building 2. When I opened the relationship editor, my Location_ID's were not connected (not sure why, seemed to be working) so I connected them. The line has no indicators for one or many.

To clear up my original question, What I am trying to do is sort (that's the word I wanted, not filter) my words instead of the ID number
Example:
I have
ID - Name
1 - Cisco
2 - Acer
3 - Dell

I want
2 - Acer
1 - Cisco
3 - Dell

[orange]

Are you referring to the modified copy I posted?This is in error I did not post a database.

An item only exists in 1 location.
A Location may contain Many Items.

I added the referential integrity (1 to Many lines) in my copy.

It seems you want to sort /Order by Brand_Name ascending.

[pyromaniac511]

I'm on a restricted network so if there was an attachment I may not be seeing it. I did see the image and have made mine look like yours. Now that I look at the image again, it makes perfect sense. You said

An Item may exist in Many Locations

and that threw me off.

Yes, I want sort /Order by Brand_Name ascending. The way I have it coded, tblBrand is reached by a nested dLookup. I need to make a way to get the sort button to do the same... I think I'm on to something, I'm going to go try it.

[orange]

You are correct, I did not attach a database. I was helping another poster on a different subject and did send him a database.

Re this thread, I only modified your relationships -set referential integrity and removed Location name from Item.

Sorry for any confusion.

Can you show the code re

Yes, I want sort /Order by Brand_Name ascending. The way I have it coded, tblBrand is reached by a nested dLookup. I need to make a way to get the sort button to do the same... I think I'm on to something, I'm going to go try it.

[pyromaniac511]

removed Location name from Item.

Not sure what you mean by this. Are you talking about Item_Location?
I need to change Item_Location to Item_LocationNotes since it is a text box that the user can put things like shelf numbers or specifics on where it is in the room. I will admit, What I have is a poor name and has tripped me up more than once.

Location_ID refers to the tblLocation's ID and Item_Location (Soon to be Item_LocationNotes) is a text field to write notes.

Here is my last working code for the sort button, I broke it trying to make it dig down (I just ordered a Raspberry Pi.... I may be slightly distracted).

Private Sub btnFilterLocation_Click()
    If Me.OrderBy = "[Location_ID]" Then
    Me.OrderBy = "[Location_ID] DESC"
    Else
    Me.OrderBy = "[Location_ID]"
    Me.OrderByOn = True
    End If
End Sub

I'm focusing now, I swear.

[orange]

Yes it was Item_Location. That field can be referenced via the Location_ID.

Before you simply change the name of that field in your database, make sure of the business rule
surrounding Item_LocationNotes. Since you called it Notes, it seems to be plural. If 1 Item can have more than 1 Item_LocationNotes, then you have a 1 to Many relationship. If that is true, then I suggest you make a new table
along these lines with as much detail as you will need

tblItem_LocationNotes
Item_LocationNoteID ' Primary key
Item_LocationNote 'the actual note/comment/text
Item_LocationNoteDate 'the date it was created
Item_LocationNoteCreator 'the creator of the note

and add a Long Integer field in tblItems to contain the FK Item_LocationNoteID.

[ssanfu]

Looking at your dB, the form "Invetory" is based on a table. Since it appears this is just to view records, you should use a query for the form record source. Then you can use code to sort the records.

The sort buttons now work. Hope the example coding will help you....


BTW, you had "Item(s)" in the header of most of the forms. The red headers, I changed the wording.

[pyromaniac511]

I just got back from my weekend and saw this, PERFECT. I'm going to tear this apart and see how you did it. As for the labels, I most likely let them auto name. At some point I intend to work on aesthetics but have been focusing on function.

Regarding notes table, since the notes can be very specific to the item would that justify a table? I was thinking big blocks of texts would be more appropriate. In that block you could have multiple notes such as "Big red tool chest - 3rd drawer on left - in back". As far as naming goes, I can certainly use note instead of notes but I'm thinking another table would be incorrect. What's your opinion?