Hi Adezii,
Your code works beautifully!
There is a minor bug but I can live with it. If you move to a transaction with no deliveries, then to a transaction where a deliveries record is populated, the first populated record is blank until you click in the box.
Again many, many thanks
Richard Penfold
Last Activity: Jul 23 '09, 10:17 PM
Joined: Jan 5 '09
Location: Melbourne - Australia
-
Hi All,
Thank you for responding.
puppydogbuddy,
Thanks for your input. I tried your suggestion but the end result is the same as mine. It does however give me the opportunity to demonstrate the problem more clearly.
If I create a criteria to filter out items with a status of sold, it works fine for new records but also blanks all previous records. Please see images attached.
ADezii,
Thanks...Leave a comment:
-
Hi ADezii,
It will take some time to get a copy of the database to you but I guess I overcomplicated the problem with my explanation. If we focus on the combo box alone I think you can help.
The dropdown list as viewed in the combo box design grid is attached to this post.
Column 1 is hidden so the combo box drop down list displays: Product, Serial Number and Status
After making a selection from...Leave a comment:
-
Hi ADezii,
Lets start at the beginning...
I have a Transactions table that has a 1-to-many relationship with Transaction Details table. Transaction Details has a 1-to-many relationship with Deliveries table. I also have an Inventory Status table that has a 1-to-many relationship with an Inventory table. The Inventory table itself has a 1-to-many relationship with the Deliveries table. Referential integrity is enforced on all joins....Leave a comment:
-
Hi ADezii,
That is correct mate. I think I have the query right but I do not know the syntax to turn the queries into an If-then-else statement that I can put into query builder.Leave a comment:
-
Hi ADezii,
You have the right idea but I don't think I gave you enough information in my first post.
Deliveries_Inve ntory is a bound control combo box
...Code:Record Source = Deliveries_Inventory Row Source = SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, [Inventory Status].InventoryStatus FROM [InventoryLeave a comment:
-
Need to filter combo box but only new records
I have a form with a subform in my database that I use to manage deliveries. A combo box on the subform displays items of inventory and thier current status. The code behind the combo box is...
A second...Code:SELECT Inventory.InventoryID, Inventory.ProductCode, Inventory.SerialNumber, [Inventory Status].InventoryStatus FROM [Inventory Status] INNER JOIN Inventory ON [Inventory Status].InventoryStatusID = Inventory.InventoryStatusID;
-
Hi NeoPa,
Your code works beautifully, many thanks.
I guess there is a VBA equivalent to the code I used but I did not find an example on the web. Does the "Dim nnn As String, nnn = and Call" wrapper to sql code work in all such instances?Leave a comment:
-
Hi,
After much brain wracking I have to admit defeat. Can you tell me what's wrong with this...
I just get a syntax error.Code:Private Sub Report_Close() Update [Transaction Details] Set OrderStatusID = 2 WHERE ((([Transaction Details].TransactionID) = [Forms]![Transactions]![Transaction Details Subform].[Form]![TransactionID])) End Sub
I have tried various combinations of bracket types and quote marks....Leave a comment:
-
Thanks NeoPa. I'll give it a go myself over the weekend and let you know the outcome.Leave a comment:
-
Half of the problem fixed
Many thanks for the help so far.
NeoPa
The Combo Box control details are as follows:
Control Source = Transactions.Or derStatusID
Row Source = SELECT [Order Status].ID, [Order Status].OrderStatus FROM [Order Status];
Format: Column Count = 2: Column Width = 0cm;2.5cm.
All
I have implemented the code suggested with one minor change as follows:
...Leave a comment:
-
Trying to update a combo box using a command button or report event
I have a form with a subform containing the combo box I want to update. I have a command button in the header of the main form that launches a report in print preview mode. I want to update the combo box from 'New' to 'Confirmed' when the report is generated. I have tried the following code in both the buttons on_click event and in the reports on_close event (security is set to trust the db)
...Code:Forms![Transactions]![Transaction Details S
-
Results of testing.
I have tried both options with mixed results. Details as follows...
Option (A)
Created unbound control Text25 on the main form.
Set Control Source of Text25 to:: = [Deliveries Transaction Subform].[Form]![OrderCodeID]
Added Link Fields as follows:
LinkMasterField s:: [Deliveries Transaction Subform].Form![TransactionDeta ilsID];Text25
LinkChildFields :: TransactionDeta ilsID;OrderCode ID...Leave a comment:
-
Clarifying my options
The value of the OrderCodeID that I want is populated into the Transaction Details table when I first create the transaction data entry. This is exposed in a control on the Details subform. From my reading so far it seems I cannot link to a subform from another subform - I must link to a control on the main from.
My options are therefore:
A. Create an unbound control on the main form where...Leave a comment:
-
Is This right?
I have been reading up on Link Master Field & Link Child field and it seems I have at least 2 issues to deal with. The controls on my forms are all named the same as the table fields; and, I may have an incorrect master/child link configuration. The first is easy I can rename all controlls on my forms. The second I am going to need some help with - assuming that's where the problem lies.
How do I determine...Leave a comment:
-
I am using form-subform to automatically complete the many side data entry. The fields on the form I create using the forms wizard, with editing, look like this:
Deliveries Form::
Main Form (Transactions Table):
TransactionID (Deleted)
CustomerID (Deleted - Replaced with 'CustomerShortN ame' Text box sourced from 'Customers' Table)
TransactionType ID (Deleted - Replaced with 'Transaction' Text box...Leave a comment:
-
Indexed field not updating
Can someone explain why a field on the many side of a one-to-many relationship, with referential integrity enforced, is not automatically updating?
I have 5 tables in my database with the following relationships:
Transactions (1-Many) Transaction Details (1-Many) Deliveries
Order Codes (1-Many) Transaction Details
Order Codes (1-Many) Inventory (1-Many) Deliveries
Order Codes (1-Many) Deliveries... -
The list box is now working well. Thanks for your time and patience.mate.
Please keep your eyes open for future postings from me. Never having developed a database before, I am sure to hit more submerged logs as I try to sail these (for me) uncharted waters.Leave a comment:
-
Seems like I didn't do a good enough job explaining how this database works. Just shows we need to test understanding at every stage - And thats with us using a common language.
The database has 3 functions
1 - Order Processing
2 - Inventory management
3 - Deliveries register
A customer order number is entered during order processing. This number is entered to the 'TransactionRef erence' field of the 'Transactions' table...Leave a comment:
-
Success!... of sorts.
I have taken a further look at event procedures in MSDN and I find that I have to type something into a text field before the 'On Change" event (or many other) works. After stumbling around with different event types I arrived at this:
Set the 'On Got Focus' event...
This will work but only if you click...Code:Private Sub TransactionReference_GotFocus() Me.List27.Requery End Sub
Leave a comment:
No activity results to display
Leave a comment: