Pop up window for order item details

[Kronix]

sigh...my first post and the whole thing gets lost because it asks for my username and password again....i'll try to rewrite it....

I'm making an Access database for a construction company. I have our products split into 3 categores -- Construction, Services, and Replacement Parts, each with its own table, because they each have different types of information. For example, the Construction table has address information that can differ from the address of the customer, along with details of the object being constructed. I use a query to combine the information from the 3 tables into one query, notably by abbreviating and concatenating the unique fields into a common "description" field. This item query can then be used as an overview list of the items in the order in Access or for a report similar to a bill.

When viewing the order in Access, I want the name and address of the customer at the top, and the item query list at the bottom. I want to be able to click on each item, or on a button next to each item, to pull up a pop-up window that contains the full details for the item from that item's table (before the item query abbreviated everything together), and be able to modify the item details and have arrow buttons to move through each item in the order. How should I go about doing this? I'm thinking of making the list at the bottom of the order form either a subform or a subreport with buttons added next to each each entry. Would it work both ways? I'm not sure how to tell the pop-up window which entry I clicked on or next to.

 

[isladogs]

Welcome to AWF - sorry you lost your first attempt.
For long posts, I recommend writing in e.g. Notepad then copying across to the forum

I want the name and address of the customer at the top, and the item query list at the bottom.

Use a form and subform or (if you must) a split form

I want to be able to click on each item, or on a button next to each item, to pull up a pop-up window that contains the full details for the item from that item's table and be able to modify the item details

Sounds easy enough, use the button or click event on the subform item to open a popup form with the records you need from the table.
DO NOT use it to open the table direct.

and have arrow buttons to move through each item in the order.

This may be more difficult. Having 2 forms open at the same time and editing records on the second can cause synchronisation issues and the dreaded 'Write conflict' errors.
Its possible to do but needs a lot of care. Recommend you avoid this bit.

 

[Kronix]

Sounds easy enough, use the button or click event on the subform item to open a popup form with the records you need from the table.
DO NOT use it to open the table direct.

How do I tell the pop-up form which record I clicked? BTW I am using the VBA DoCmd.OpenForm() function to open the pop-up, is there a simpler way? I'm currently using a field list with double click action although I'm thinking I will try a report with buttons instead.

 

[isladogs]

Just apply filter criteria when you open the form.
Something like

DoCmd.OpenForm "MyFormName", , , "PersonID ='" & Me.PersonID &"'"

Whilst you can make a report interactive, that isn't their main purpose.
I would stick to using a form.

 

[Kronix]

Thanks, I experimented with that with good results but now I have a different problem now since I've decided to first try a different organization.

I have two subforms sitting next to each other on a main form. The first is in datasheet view, the second is in single form view. The first subform is from a query which is filtered according to the order number on the main form that contains both forms. The second subform also is filtered according to the order number on the main form, but is based on the original table which holds more detailed data (which is why it is in single form view for more detail). The query for the first subform contains the primary key for the original table, so when I double-click on a field in that row I want that specific item (with the details from the original table) to show up in the second subform.

How do I move the record in the second form to the appropriate one with the primary key obtained from the first form? Keep in mind the second form only contains a subset of the records in the original table (resulting in the records seen in the query from the first form, but further broken down under different tab categories). Furthermore, I'm not sure how DoCmd commands are supposed to work since that would operate in the first form -- where the double-click action was activated -- instead of in the second form where I actually want to locate the record.

 

[isladogs]

In your double click event, you need to add code to filter the PK field of the second subform based on the PK field of the first.
I'm assuming this is called ID.

The idea is similar to the previous code except you need to know how to reference the other subform (MySubForm2)

Both subforms have the same parent form so it will be something like

Parent.Form.MySubForm2.ID = Me.ID
Parent.Form.MySubForm2.Requery

Change the subform and PK field names to suit your setup
The requery line will refresh the display.

Have a look at this link which explains the idea in more detail
http://allenbrowne.com/casu-04.html

 

[Kronix]

I ended up using a Recordset Clone with the FindFirst function and Bookmark, since I wanted to move to a record in a record set. Although I'm not sure how your example was supposed to work since that would change the ID of the currently displayed record, which normally shouldn't even be possible if it is the primary key.

Anyway, I have another thing I'm curious whether I am doing optimally. So the order items are in a list box (which is in its own subform) and clicking an item in the list box brings up the details in the details subform next to it as I have already described in the previous post. However, clicking the arrows to change the records in the detail subform should also result in the appropriate item in the list form being highlighted (i.e. it works both ways).

Well, I got that to work, but I found that I had the runtime error 2455 every time I first opened the database/main form, saying that the list box being referenced was invalid. So I figured maybe the details subform is being loaded before the list box subform, and the attempt to change the list box (i'm using the Form_Current event) at the start results in an error because it does not exist yet.

So I created a non-visible text field in the main form that the details subform copies the key into in its Form_Current event. The main form's Form_Current event then uses the hidden field to highlight the appropriate item in the listbox subform.

This works! But it sure seems messy and doesn't seem to follow any predictable rule. It actually appears that the execution of events would have to happen in the following order:

1. details subform's Form_Current event copies key into main form's hidden field.
2. listbox subform gets populated
3. main form's Form_Current event uses the key which was copied in step 1 to highlight the appropriate item in the listbox.

This order makes no sense to me.

So, is this actually a load-time problem as I think it is or something else with a better solution?

 

[Kronix]

so it said a mod had to approve my message and it's still not here.

edit: ok, thanks, there it is above. hopefully somebody can help me out.

 

[MarkK]

Sorry Kronix. This is apparently a bug with the site, that posts randomly need to be moderated. Thanks for you patience and sorry for the inconvenience.
Mark

 

[Minty]

Subforms controls are populated before the main forms.
The actual event orders are listed here https://support.office.com/en-gb/ar...-objects-e76fbbfe-6180-4a52-8787-ce86553682f9

 

[Kronix]

Minty, but how do I find out in which order the subforms on the main form are being loaded?

On another note, I'm wondering if someone can explain why something happened the way it did.

I have a list box in a subform on my main form, based on a query of orders matching the order id on the main form. The list box lists each of the items in the order. I have the order details subform next to it in the main form, listing details for each of the items in the listbox. Whenever a record is added to the order (through the details subform), the list box is requeried to show the item in the list

Well, I had a problem where if i added a new order (on the main form), resulting in a blank listbox, and then added my first item in the details subform, the listbox would still stay blank. The listbox would update after I moved to a whole other order on the main form and back, though. I solved it be putting a Requery of the subform that contains the listbox before the Requery for the listbox in the VBA code.

So, for some reason I need to Requery both the listbox and the subform it is in to update the listbox for a new record that I have not yet moved away from. Any idea why this is?

 

[Minty]

List boxes are nearly always effectively unbound controls , they have their own datasource (rowsource), so re-querying the Subform wouldn't necessarily requery the listbox.

I think.

 

[Kronix]

Well requerying the subform it's in obviously made a difference in this case. BTW, I edited the first line of the post for your previous post about load orders.

edit: I also just noticed that when the list box is "blank", it's really not even there. Both its border and the border for its description box that I left halfway hidden behind it do not appear. It appears the subform requery actually makes the listbox and its description field appear in the first place. I confirmed this buy putting a msgbox pop-up between the query for the subform and the query for the listbox.

 

[Kronix]

How do I make fields in a new record (single record display) default to a certain value without having the record checks activated so that it gives me an error for required fields if I try to leave the record without filling in all the blanks? If that's not possible, how would I give certain fields default values as soon as something is entered in another field? Is there an event for having one field entered, or maybe some way to check that the primary key for the new record has been assigned (which, as always, happens as soon as a single field in the new record has been filled, although I'd like to know for other reasons if it's possible to prevent that from happening). I've also noticed that AfterUpdate events for controls don't activate if the value was assigned by code, only when it was typed in.

edit: I don't mean standard values in the property sheet. I mean assigning values in code.

 

[Minty]

Events are not triggered by any values set in code.
This is by design, as it would be very easy to get infinite loops with controls referencing each other over and over again.

If you use the default values property these are effectively not saved until something is added to a record. You can set default values in code.

 

[Kronix]

Question: I have added a details subform to my main form. It displays details for the selected item in another subform. The details are loaded from a query and displayed in single form view.

I have done this for my first product type (Machines), but the problem is I have different product types (Machines, Services, Parts) that each use a different query to display the details. But I want to display these details in the same spot on the main form, in the same subform (or at least appear that way). What is the best way to go about doing this? I was considering changing the recordsource, but the details for each category must be displayed in a different arrangement because the details are slightly different. This means I need to have multiple subforms set up for each category.

How should I set it up so that the appropriate subform displays in the same space?

 

[Kronix]

Well I got that fixed by changing the source object.

I noticed when deleting a record it shows the next record when the confirmation dialog pops up as if the record I had selected was already deleted. If I select no then it goes back to the record I decided not to delete.

How do I make it stay showing the record I am about to delete when the confirmation dialog pops up?

 

[arnelgp]

there is no fix.
the least you can do is capture some info of the records

candidate for deletion and show it in msgbox:


Dim strInfo As String

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
If MsgBox("Are you sure you want to delete " & vbNewLine & vbNewLine & strInfo, vbYesNo) = vbYes Then
Response = 0
Else
Cancel = True
End If
strInfo = ""
End Sub

Private Sub Form_Delete(Cancel As Integer)
strInfo = strInfo & "Record: " & Me.ID & vbNewLine
End Sub