How to auto populate a form

[e51lrrp]

OH my...thank you so much for validating what I already suspected. And Yes nothing he sells is standard everything is custom. He even designs logos for people and businesses. He is willing to work with whatever I come up with so right now I am saying that these customer/organizations with have several order numbers all due on the same day.

Also I have already told him that he MUST give everyone an Organizational name....using the last name as his Organization. THANKS again for brain storming with me. I have not gotten all of my reports and forms to work exactly as I hope they will so probably will be asking again for your assistance. By the way is there a problem with my thinking that populating the customer form with the cbobox and then clicking on the order button to then give the old customer and new order a problem? I would then button to detail form. I haven't really got that to work right as you know....thanks S

[June7]

Use an unbound combobox to input criteria for search. This combobox could show the organization name but the search will be for the AccountID.

A parameterized query is one method for finding existing record. Review:
http://datapigtechnologies.com/flash...tomfilter.html
http://datapigtechnologies.com/flash...combobox3.html

Another method would construct a criteria string and set the form Filter and FilterOn properties.
http://www.allenbrowne.com/ser-62code.html

[e51lrrp]

June7:
I have been working on the above and have the cboFind working great. I still need some assistance to figure out why my three forms won't work the way I would like them to. I am still getting the Join problem. Is there any way you can look at the database and give me more suggestions. Seem like the more and harder I work the more frustrated I get....keeping me up at night. Thanks so much for your consideration in this endeavor. S

[June7]

The subforms should be opened associated with an existing order and order is associated with existing customer/organization. OrderID and CustomerID and Organization should not be user editable on subforms. Change all three to Locked textboxes. Remove the DefaultValue from CustomerID and Organization.

Change RecordSource of forms to RIGHT join instead of INNER.

FROM (tblCustomer RIGHT JOIN tblOrders ON tblCustomer.[CustomerID] = tblOrders.CustomerID) RIGHT JOIN tblEngravingTrophiesDetails ON tblOrders.[OrderID] = tblEngravingTrophiesDetails.OrderID

There will also be an issue if new order is initiated for a new customer before customer record is committed to table. Why is OrderID on frmOrders a combobox? OrderID is an autonumber field. This should be a locked textbox. So should CustomerID and Organization (remove DefaultValue). The RecordSource for frmOrders should also be RIGHT join.

Unfortunately, users can click buttons without even entering parent record data.

I was really getting frustrated because the subforms were populating the OrderID with 0. I even converted all the macros to VBA and got more weirdness. I had converted the OrderID combobox on frmOrders to a textbox but apparently that corrupted the control because after I deleted and recreated the OrderID textbox on frmOrders, subforms worked.

Opening subforms independently instead of as actual subforms on a main form is always tricky - something I avoid.

[e51lrrp]

June7:
I am sitting here crying my eyes out with happiness as your suggestions for my forms ALL worked. I CAN'T thank you enough. Even my Customer Receipt work wonderfully. Can you explain why Right Joins worked and not INNer Joins? or was it because I started using combo boxes or both???

You mentioned the subforms. I really hadn't incorporated them in to my Customer form because I couldn't make the main form work and didn't want to go the next step and put the details in the Customer Form until I was sure I could make it work.

I have been working on this for over 7 months and have been embarrassed at times to ask questions. You have been nothing but patient and for those of us out here struggling you are a blessing. I would send you something if I knew where you were so just know that TODAY YOU MADE AN OLD LADY VERY HAPPy.....

[June7]

INNER join requires related records in both tables for data to display. Attempt to create the child half of relationship prevented by INNER join.

With normal form/subform arrangement, the RecordSource would not need to join tables because the parent info would be on the main form and no need to display on subform.

[e51lrrp]

THANKS for the explanation. Super Helpful. Sharon

[e51lrrp]

June7:
You have been so helpful to me I am almost done with the all the elements of my database and I want to thank you again for helping me.

I do have one more issue I would like help with. I have the frmCustomer with the subform working well and I want to take it one step further, if possible. I would like to be able to select an OrderID from the subform and be able to see the details of that order. Normally I think it would be straight forward except in my case where I have the details separated in to three queries

Some orders have qryApparelOrderDetails , qryEngravingTrophiesDetails and qryPlaquesEngraving.

Is it possible to write code that would go to a report for ApparelOrderDetails , PlaquesEngravingDetails OR TrophiesDetails based on TypeOfOrder.

Apparel has: Embroidery, ScreenPrinting and Transfer where Plaques and Engraving are associated with PlaquesEngravingDetails and Trophies are associated with TrophiesDetails.

I have attached my latest and greatest database for your perusal.
Thanks Again.
Sharon

[June7]

Yes, code can be written to open appropriate report based on some value. Could use a Select Case statement.

Your file did not attach.

[e51lrrp]

Sorry Forgot to attach....thanks for your consideration. Sharon

[June7]

Did you attempt code as suggested?

[e51lrrp]

Problem...I don't know any coding...I did look up your Select Case Statement but it didn't help much. s

[June7]

Do you want to open a form or a report? The code depends on how the forms/reports are named and whether the TypeOfOrder codes are within the form/report name. At its simplest code behind a button on subform could just concatenate the TypeofCode value to construct the form/report name:

DoCmd.OpenForm "rpt" & Me.TypeOfOrder & "OrderForm"

However, sounds like you have a more complicated structure.

Dim strForm As String
Select Case Me.TypeOfCode
Case "Embroidery", "ScreenPrinting", "Transfer"
strForm = "Apparel"
Case "Plaques", "Engraving"
strForm = "PlaquesEngravingDetails"
Case "Trophies"
strForm = "TrophiesDetails"
End Select
DoCmd.OpenForm strForm

[e51lrrp]

June7:
Yes the second coding is more what I need. I did purchase a VBA book and hopefully I can use what you have given and using the book can make this work. Again, I appreciate your assistance. Sharon

[e51lrrp]

June7:
I have coded my new button on my sub_form but nothing happens when I active it. It doesn't even give me an error message. How does the new module know which orderID to apply to? Don't I have to tell it that the current highlighted order should have the module applied to it? I have the worst time figuring out how Access know which item is affected. For instance how do you single out one order from a group of orders?


Private Sub View_Click()


Dim strForm As String
Select Case Me.TypeOfOrder
Case "Embroidery", "ScreenPrinting", "Transfer"
strForm = "frmNewApparelOrderDetail"
Case "Plaques", "Engraving"
strForm = "frmPlaquesEngravingDetails"
Case "Trophies"
strForm = "frmTrophyDetails"
End Select
End Sub