How to auto populate a form

[June7]

The code is working. However, all it does is set the value of variable strForm. The code does nothing with the variable. What do you want - to open a form or report?
DoCmd.OpenForm strForm, , , "OrderID=" & Me.OrderID

BUT, the form opens with #Name? in textboxes. frmCustomers_sfrmOrders is open to a record Transfer type with OrderID 1. There is no OrderID 1 in qryApparelOrderDetails. I moved to OrderID 5 and form opens correctly.

Need to fix data. Time to purge and start from empty?

If you set breakpoint in the procedure you will see the execution. Refer to link at bottom of my post for debugging guidelines.

[e51lrrp]

June7:

Mega questions this morning....received you help and as I suspected I was not telling Access what to apply the coding to....That was there from a long time ago and I am not even sure what it does except limit the number of OrderID....I think.

When you said to fix data what are referring to? Did you mean to reset all the orderID, CustomerID and duedate that is being applied during the order processing?

One last thing. I have tried repeatedly to debug and nothing happens. I even stepped in to the debug process. Below is my attempt at coding. Wouldn't you have to tell access first to open the form THEN apply the strform variation? How would you do that with the Then statement?

Thanks again for all your assistance. PS...Happy Presidents day...if you are in the States. Not sure where you are? Sharon


Option Compare Database
Option Explicit

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"


DoCmd.OpenForm strForm


End Select


DoCmd.OpenForm strForm, , , "OrderID=" & Me.OrderID
End Sub

[June7]

There is an OrderID 1 in tblOrders and it is identified as Transfer type but there is no OrderID 1 in tblApparelOrderDetails. Why not?

No, do not open the form without the WHERE CONDITION argument. Remove the first DoCmd line.

[e51lrrp]

June7: Apparently along the way I deleted the detail part of OrderID 1. I have now created it and it does open the detail record. What is happening now is the any order listed in the sub_form that has embroidery, screenprinting or transfer work fine. However the rest of the case argument does not. I tried inserting a ; or , and that flashes as an error. How do you tell access to keep going down the strForm sequence when module is activated?

So Close...this is my last part of the database. Feel pretty good about the rest.
Thanks again for all your assistance.
sharon

[June7]

I've lost track of this issue.

Do you want to provide latest version of db?

Which form is involved?

[e51lrrp]

June7:
Been trying to wrap this program up in a neat little package BUT still have a few issues. Mainly using the detail forms. Basically the frmTrophieDetail work great but the other two forms frmNewApparelOrderDetail and frmPlaquesEngravingDetail does NOT. I have studies but of the sql statements and the only things different is the WHERE CLAUSE. The error message is the same JOIN problems I have had and you suggested the RIGHT JOIN Option. That worked for a while and I haven't changed anything but now it won't allow me to populate the detail form. I gets stored in the tblOrders so it must be in my queries but for the life of me I can't see where the problem is.

Sure would appreciate it if you could give me a hand on this one.....THANKs in advance for your assistance.
S

[June7]

Look at relationships in the Relationships builder.

The relationship between tblEngravingTrophiesDetails and tblOrders is "Include ALL records from 'tblOrder'". Between tblApparelOrderDetails and tblOrders it is "Include ALL records from tblApparelOrderDetails".

Now look at the links in the 3 Details queries. Notice the link between tblCustomer and tblOrders is different in qryPlaquesEngravingDetails because it does not show the many (infinity) symbol as the other 2 do. I don't know why. Delete tblCustomer from the query and reinsert and fix the join type. The many symbol now shows.

Now go check the macros for each Details button for consistency. Since the queries are consistent, issue must be with code that opens the forms.

[e51lrrp]

June7:
Hello June7...I am back at it and running out of time on my project. I have rebuilt my qryApparelOrderDetails but it does not produce the results needed. Can you take a look at it and see if you can make it work? It is the same setup as the qryTrophiesDetails. The Trophies query works but NOT the ApparleOrderDetails. I have been working on this for so long but I now have the infinity symbol on both relationships queries. I sure would appreciate your assistance. Sharon
I can't believe this should be so difficult. I must be missing something. I can see that the data get to the Orders table but the query won't pick it up. Look at OrderID 133 CustomerID 76....

[June7]

There is no record in tblApparelOrderDetails for OrderID 133. I manually created record in the table and it shows in the query.

As stated in post 21, INNER join requires associated records in both tables for record to display in query. Your queries have 3 tables with INNER joins.

The Apparel Details button on frmOrders errors with message: The record source 'qryApparelOrdersDetails2' specified on this form does not exist.

[e51lrrp]

June7:
I am in Michigan with my son but I am still having problems with my form frmApparelOrderDetail. I know the problems is the query qryApparelOrderDetail but cannot figure out what is happening. When I join the orders table (Left Join) I don't see the OrderID. The only way it displays is if I take it from the tblOrders but it then becomes New and I know that is not correct. Can you look at these to items and see if you can help me with this. I have eliminated all old data and have just one customer listed to help diagnosis this problem.

I sure would appreciate any assistance you can offer. Again thank you so much for all you help in the past...
Sharon

[June7]

Out of town til 3/26. Can't review database. If you still need help by then, add post after 3/26.

[e51lrrp]

I will be leaving the area on the 27th so I hope I can find a resolution before then but if not I sure hope you can look at it then and I will have to leave the database partially unfinished. Have fun if you are going on vacation. s

[June7]

If you add post after 3/26 that will get my attention, otherwise I will lose track of this thread. I am not keeping a list of thread to revisit.

Yes, on vacation.