Page 1 of 5 12345 LastLast
Results 1 to 15 of 66
  1. #1
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35

    Form to Output Table

    I am creating a database for cash receipts. Basic need from the database is 1 form for entry, ability to add to supporting tables through a form, reports. I created a budget line table, employee table, and a payment type table. The need on the form is for employees entering to only see the current form, be able to choose a budget line, choose an employee, choose a payment type, fill in the rest of the general information, have an auto number. I previously built it based on my cash receipts entry table hooked to the form but you could see/change all receipts. So I am trying to build it with the form coming from the budget line table, the employee table, and the payment table, output going to a receipts output table. I can get the form up from them. I created the output table. If I make the output table the source for the form they can see/edit all the receipts again. I would like to have the options on the form to save a record, print the record, and possible close the record. If I get the output table I know what I need to do to create the reports I need as I did it before. I tried putting a command button on the table but I am not sure what I need attached to it. I have been trying different macros and builds but nothing seems to be exactly what I am looking for. Does anyone have any thoughts? If I need to change this form around I can I just really need some insight.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    my cash receipts entry table hooked to the form but you could see/change all receipts
    You could just change the Data Entry property of the form to Yes and then your users will only be able to add/see new records. Now if they have 3 new records, they can add each of those in one open event of the form and have access (to change them) until the form is closed. Once the form is closed and then reopened, they will no longer see the records they have added previously.

    The form would be bound to your receipts table and you would have combo boxes for budget line, employee and payment type. The combo boxes would each be bound to their respective tables.

    I tried putting a command button on the table but I am not sure what I need attached to it.
    You cannot put a command button on a table, but you can put it on your form.

    I have attached a sample database that I think illustrates what you want. Just open the form and see if it is what you want.

  3. #3
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    You know I read on here about making it data entry - which I did. but I didn't realize the close the form & come back part. So that definite helps. I had a typo on the command button, meant I tried to put it to the form. I will look at the information you gave me on that.
    One more question - the budget line table has 2 fields, the budgetline & the description. I currently don't have a key because the budgetline would be the best key but has in some cases multiple descriptions. On my form I want it to be so that the user pulls up the budget line & only sees the related description as an option. Currently that happens - for the first form. I did it with a query so that if the budget line on the form matches the budget line in the table it populates the correct description. If they enter multiple receipts without closing the second time they can still choose from all budget lines but it gives them the description from the previous receipt. Do I need to break up that table so that the budget line can be a key?

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I currently don't have a key because the budgetline would be the best key but has in some cases multiple descriptions.
    If a budget line has multiple descriptions, that describes a one-to-many relationship which warrants a second but related table per normalization rules

    tblBudgetLines
    -pkBudgetLineID primary key, autonumber
    -BudgetLineNumber

    tblBudgetLineDescriptions
    -pkBudgetLineDescID primary key, autonumber
    -fkBudgetLineID foreign key to tblBudgetLines
    -txtDescription

    With this structure you would store the value of pkBudgetLineDescID as a foreign key in your receipt table rather than the value of pkBudgetLineID. You would probably want to use what are called cascading combo boxes to select the budget line number from the first combo box and then select from the related filtered descriptions in the second combo box.

  5. #5
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    Thank you. I am going to try to re-work my budget line tables Monday & see what happens. I will let you know.

  6. #6
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    I believe I fixed the tables. I have a budget line table which only has the budgetline & it is the key. I have a description table which includes the budget line & description, related to the budgetline table on a one to many relationship. The description is the key. When I hook the budgetline to the formI do the row source to the budget line table, when I hook the description to the formI think I am doing it wrong because it will only work once. I did a query & I did the description and in field 2 I did the form combo box for budget line where it matches the budget line in the budget line table? Do you know what I may be missing in my form to make it work if I change my mind & try to do a different budget line?

  7. #7
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I'm not quite following what you are saying. You have the budgetline field in both tables. The budgetline field in the bugetline table is the primary key. The budgetline field in the description table is the foreign key. That part is correct.

    You then need to join the budgetline field of the budgetline table with the budgetline field of the description table. The description table should be on the many side of the relationship.

    I would not recommend using a combo or listbox at the table level; the combo/list boxes are best left for forms. For more detail as to why, see this site

    I would generally recommend number fields to be primary and foreign key fields since databases are generally more efficient with number fields rather than text fields.

    For an example of cascading combo boxes, Bob Larsen has one on his site

  8. #8
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    Sorry I wasn't very clear, plus I edited my post after you read it prior to your response as I had a typo. 1 table (budgetline) has 1 field called budget line it is the key & it is something like this A 1234 4561.1020 Moms Program. The second table has 2 fields it has budgetline & description it would have many on the budget lines there could be A 1234 4561.1020 with a description of Moms Program fees, and the A 1234 4561.1020 with a description of Moms Program Donations. I made the description the primary key, but can remove that. I hooked both fields to my form as combo boxes. The combo box on the form for budget line hooks back to the budget line table. The combo box for description I used a query. In the query I put description but then I put next to it budget line (where) -combo box budget line = budget line from the table ( I would specify the table but I tried both the budget line table & the description table & both gave me the same results). I am in the receipt, I pick a budget line, go to the description & I see the appropriate descriptions. I choose one. Then I decided - wrong one - so I take the description out of the box, go back up to budget line & choose a different one, but when I go back to descriptions I still see the same ones from the previous budget line. Does that make sense?

  9. #9
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    If you check the site I mentioned in my previous post regarding cascading combo boxes that should help resolve the issue. I believe you need to requery the second combo box. To do this, you need to add some Visual Basic for Application (VBA) code to the After Update event of the first combo box. The code would look something like this:

    Code:
    me.secondcomboboxname.requery

  10. #10
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    Perfect! Thanks. I just need to use the other information you gave me to set my button on the form. Thank you so much.

  11. #11
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome.

  12. #12
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    My form looks great & works wonderful. That being said I am on to Form #2 & I am stuck. I have to have a form that relates to the table (ReceiptOutput) that has been populated with my previous form. This form is going to be a void form. The user will pull up this form put in a receipt number or choose a receipt number & it should pull up fields from the ReceiptOutput table that are related to that receipt number. They should be able to check mark void & have that save back to the receipt output table. I was looking at previously mentioned sites on how to fill in in forms I went to that site & downloaded a sample database. I was attempted to relate it to mine but seem to be lost somewhere because it's not filling in. It allows me to choose the receipt number but nothing fills in. Can you help me one more time?

  13. #13
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    They should be able to check mark void & have that save back to the receipt output table.
    Based on the above, I assume that you have a yes/no field in the ReceiptOutput table.

    I would create a form bound to the ReceiptOutput table and then in the header section of the form add an unbound combobox (unbound means that combo box does not have a control source). The combo box's row source would be the ReceiptOutput table. You would include the primary key field of the table (hidden) and the receipt number field. Then in the After Update event of the combo box, you would have code similar to the following:

    Code:
    'clone the form's table/query into a recordset
    Dim myrecset As Object
    Set myrecset = Me.RecordsetClone.Clone
    'find first matching record in the recordset
    myrecset.FindFirst "[NameOfThePrimaryKeyFieldInYourReceiptOutputtable]=" & Me![NameOfTheComboBoxInTheHeaderOfTheForm]
    'set the form's record to the found record
    Me.Bookmark = myrecset.Bookmark

  14. #14
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    will it matter if the receipt number is the key field in that table?

  15. #15
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    will it matter if the receipt number is the key field in that table?
    You're combo box would obviously have only the 1 field.

    If the datatype of the receipt number is text and not numeric, one line of the code will need to be changed to the following:

    Code:
    myrecset.FindFirst "[NameOfThePrimaryKeyFieldInYourReceiptOutputtable]='" & Me![NameOfTheComboBoxInTheHeaderOfTheForm] & "'"

Page 1 of 5 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formatted Excel Output
    By jerryrs in forum Import/Export Data
    Replies: 6
    Last Post: 02-26-2011, 11:58 PM
  2. Replies: 3
    Last Post: 01-10-2011, 10:31 AM
  3. Replies: 4
    Last Post: 10-03-2010, 09:54 PM
  4. Help combining two quieries to output
    By AccessUser1234 in forum Access
    Replies: 3
    Last Post: 04-01-2010, 01:59 PM
  5. Insert Query output into a table
    By ammu_sridhar in forum Programming
    Replies: 1
    Last Post: 06-12-2009, 01:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums