Sorting my Form 1

[NewfieSarah]

--------------------------------------------------------------------------------

Hey all I have a problem I would like to populate my forms with the data from another form, and I know one way of how to do this however, I would like to be able to sort my form data. Since I have filtered my data I only see user 3's reccords not all users on the one form, I would like to have the ability to sort the form by the most current date. Since I already have more forms done, i would like to keep it that way. The fields I have on my form are:
PIN, PERMIT NUM, DATE, PHONE, PHONEWORK, OWNER, AGENT, CONTRACTOR, DECSCRIPTION, WIDTH, LENGTH, CLASS, DATE ISS, PENDING, HEIGHT, COST, NOTES. . . IN that order! So what I would like to do is put them in order by the date recored ie DATE. so that the most recent date shows up not Actually now just looking at it i think it is sorted by PERMIT NUM, so is there a way to sort by two different things? as well or what? Thanks in advance

 

[NewfieSarah]

Just another note maybe it is possible to sort my filtered data?? Any ideas on that? Since my form(Sub Forms) are filtered data, I would like to sort those fields. I have the data filtered, however I would like to it in order by date. Since it is just showing the data how it was added in the table, so there would bt 1978-1999-2001-1975-1999-2005 and I would like to to display asd 2005-2001-1999-1999-1978-1975 Thanks

 

[MajP]

This simplest way is to build a query for your form. You can sort by as many fields that you want. They just have to be in order from left to right if using the query builder. You can drag and drop fields to reorder in the query builder. Your data will sort properly if it is filtered or not.
Do the same with your subform.

 

[TheAceMan1]

How are ya NewfieSarah . . .

Agree with [blue]MajP[/blue]. Have a look here: thread702-1174719

See Ya! . . . . . .

 

[NewfieSarah]

Thanks for the input MajP and TheAceMan1 However I am not sure I want to do it the "Simplest way" LOL I would really like just one thing in this program that i am working on to go the way that i want. but I am keeping positive to see if that will happen. Thanks again

 

[MajP]

After rereading what you said, maybe I understand more what you are asking. Now I think you are saying you want to sort a form using the menu bar (not code) and then based on that sort order open another form. If that is what you want to do.
On form one

Private Sub Command1_Click()
DoCmd.OpenForm "frm2"
Forms("frm2").OrderByOn = True
Forms("frm2").OrderBy = Me.OrderBy

End Sub

So I think I understand now the other part of your question. You are asking can you use the toolbar to sort on multiple fields. I think only in a datasheet view. So you can pull up a datasheet view, order the fields, sort them, then pop open another form using that sort order.

 

[NewfieSarah]

No That is not what I want to do actually. Okay let's see how I can go about this. What is to add a new record, with fields that are common already populated that is what I am looking for. Now my main page has all the records listed. so i find the record i would like to work with and click on the page i would like to work with since that page holds data on that record. Now this form is filtered to that specific record, Now here I can change and add things on the record,records for this specific record(Person).

However I have tryed many different way to have the data added to the form and since the data is not displayed yet I have no where but the tables to copy it from. So I am trying to find different ways to copy the data to the form so the user doesnt have to enter the same data about themselves ever time the want to add a new record. Understand a bit better now??

So that is why I was asking about Sorting on my form, since I would like to add a new record and i was trying a differnt way to have the owner name appear. that way being that i would copy the data from the first form to the other forms, however I dont know how I will populate the first since I have to take it from the table and I havent figured that out either.

However when I put the value from the first form to the others for the owner then i get the first records owner, not the current. Since I am dealing with houses they have had many owners but they are still the same house. So I would want to sort it so the most current was frist so I then could copy that to the other pages. Thanks for your time, any ideas on what I have said would be great. Thanks

 

[MajP]

Ok. It sounds like you may have a normalization problem. If you design your db correctly you should never have to enter the same information multiple times. Copying this information multiple times is a bandaid not a solution. If you are trying to copy the same information repeatedly, likely you have a design problem.
For example it sounds like your table is based on "People". Lets say you want to add information about "Projects" that they are working on. One person could have many projects. I would have a projects table, and a key to reference which person is assigned this project, but the persons information is only entered once.
Tell us about your tables and what you want to do. Not how you are trying to do it.

 

[NewfieSarah]

Okay, I have PID table, a building table, water table, documents table... The main form is based on the pid table. the building form, water form and documents form are based on the building, water and documents tables. What i have done is commancd buttons on the Main form that link me to the building, water, and documents forms. These forms are filterd to show only a specific amount of data from the main form. i want to take the data from my main form and add it to my other forms through code. so from PID table, ARP, PRD, tables I can take Owner, number, date, class from those and put it on water and building forms. then save it to the tables.
So take A from table 1 copy and put A into textbox on form and from textbox to table 2.

 

[MajP]

Let me see if I got your buisness rules. Your PID table I think means Permit Identifier. A permit has an owner, a permit number, a date of the permit, and a permit class

PID Table
Owner
number
date
Class

Now here is my question. Is a permit in your model associated to a building, water, and documents? This will tell me which type of relationship you have.
1. Does a buiding have one permit?
2. Does a building have many permits?
3. Can a building share a permit with another building?
4. Can one permit be used by several buildings.

Also what fields are you trying to copy the above information into, in what tables. We do not need to know what you want to copy in code (I do not think you want to do this), but need to know how these tables are related.

 

[NewfieSarah]

PID- id, pin, address, prov, postal, phone, lot num and class,
From here I can click on my command buttons for different forms
building, docs, assessments, etc...

1-Building have more then one permit.
2- building do not share permits

The fields i want to copy(populate) are phone, owner.
These fields are located in arlist table. Which is the assessment form.

Why is access different from other programs?? I have copyed data many times in VB Why do people things that you shouldnt copy data in acess??

 

[MajP]

It is not an Acess issues, but a relational database design issue. You probably need to read

http://www.alphabetcitydataworks.com/fundamentals.htm

Sure, there are times when you do need to copy data, but often it is a sign of poor database design. This looks like one of those cases.

So what you described is there is a one-to-many relationship between buildings and permits. If a building has information about an owner, then you do not need to put this information in a permit. Read about subforms. But it sounds to me like you want to relate many permits to a building.
If you use the same Agents, and contractors often. And owners have multiple buildings that they own. Then I would probably have
tblOwners with owner information
tblContractors with contractor information
tblAgents with agent information
tblBuilding with building information
tblPID with information specific to a permit not contained in these other tables, but a key referring to these tables.

Read the link, it may seem clear.

 

[NewfieSarah]

I know all about realtaion database design I covered that topic when I did my trade in computers. Thanks for the info though. However I did not create this database/program from the bottom up I was given it to finish/touch up.

 

[MajP]

Maybe something like this. If you are trying to add a new record from the Main form on form ARP.

Private Sub Form_Click()
  DoCmd.OpenForm "frmARP", , , , acFormAdd
  Forms("frmArp").txtBxOwner = Me.txtBxOwner
  Forms("frmArp").txtBxPhone = Me.txtBxPhone
End Sub

 

[NewfieSarah]

humm that code doesnt really make any sence, I am not trying to add a record to Arp. I am trying to add a record to building with the data from arp table. copy the form apr owner to the form apr owner textbox. humm nope!!

 

[MajP]

Easy now, just trying to help. Maybe something like ,

Private Sub Form_Click()
  DoCmd.OpenForm "frmBuilding", , , , acFormAdd
  Forms("frmBuilding").txtBxOwner = Forms("Arp").txtBxOwner
  Forms("frmBuilding").txtBxPhone = Forms("Arp")Me.txtBxPhone
End Sub

What code do you have already? It may be easier to see what is not working in your current code.

 

[NewfieSarah]

Yes I know how to copy the code from one form to another form. However what i would like to know is how to copy code from my table to the form. The code I used to copy from the building form to the arp form was:
OWNER.Value = Form_Building.OWNER.Value That was easy, i could use that however I dont know how to copy from the table to the building form. since in my add on the building form i would like to do the same but i cnat copy from the form since there will be no data when i add a new record. Also the other thing with using that code, is that i dont get the up-to-date record. I get the first record in the lot, which is not good cause the person could have sold there house and have a new owner in 2005 and now i would like to have the 2005 owner not the 1999 owner. Correct? Sorry for being a bit pushy

 

[MajP]

Maybe something like?

Public Sub subCopyToFormFromTable()
  Dim strOwner As String
  Dim strPhone As String
  Dim rst As DAO.Recordset
  Dim strSql As String
  'sort the arp table by date
  strSql = "select Owner, Phone from tblArp where whatever your criteria is orderby Date"
  rst = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  rst.MoveLast
  strOwner = rst.Fields("Owner")
  strPhone = rst.Fields("Phone")
  rst.Close
  'assuming you have the form you need open
  Forms("frmName").Owner = strOwner
  Forms("frmName").phone = strPhone
End Sub

 

[NewfieSarah]

Thanks for the code MajP. This is a good start. Although i am not sure what to use as criteria yet. Thanks