I have an order form with an order details sub form. Both are bound to their respective tables and function correctly, with the OrderID being inserted automatically into the order details table.
However I am using VBA to update and check stock and have it working when attached to a separate form called process orders. Which event would be the best place to place this code. I do not really want to have the process orders form. Would it be on the master form or the sub form. I thought of placing it in the after insert event on the subform, but there can be many order items per order and it will run before I have finished adding order items. I tried it in the after insert event on the master form, but I think that this will run the code too soon as at this stage the order details will not have been written to that table.
I have a DB with TblInspector and TblViolation Data. Here is what I am having trouble with. On my form I have a combo box named Inspector and a text box named ARNumber. What I would like to have happen is anytime that I select John Doe from combo box Inspector it pulls his ARNumber from TblInspector and saves his ARNumber in TblViolation Data.
I have fields Inspector and ARNumber in my TblViolation Data. I am pretty sure it is an After Update Event. I just don't know what code to put where. This is what I currently have on my combo box. It pulls the number and displays it in the text box on the form but does not save it to the table.
Private Sub Inspector_AfterUpdate() Me.ARNumber = Me.Inspector.Column(1) End Sub
I am trying to use a Combobox BeforeUpdate Event to achieve a required result.
I have two combo boxes inside a frame. The first is a year number 2011, 2012, 2013, 2014
The Second is the Week No ranging from 1-52, but 1-53 in some cases and this updates accordingly with the year number selected.
When the user has selected Week 53 and then changes the year to a year where only 52 weeks exist I want to Cancel their event.
I have already made a function to determine if 53 weeks exits however when I come to do the Cancel Event, the combobox for the year doesn't change back. It stays on the users new selection.
Below is the code that I am trying to use:
Private Sub cmbWeekNoFromYear_BeforeUpdate(Cancel As Integer) If UpdateWeekNoSelections Then Cancel = True Me.cmbWeekNoFromYear.Undo End If End Sub
'cmbWeekNoFromYear is the name of the combobox which holds the year number 'UpdateWeekNoSelections Simply returns True if I want the users change cancelled for cmbWeekNoFromYear
I have a database which is slowly evolving. Users needed a feature to delete some records without a trail and some with a trail from the form. So I added an apply action field in the subform using which they can delete a record without a trail and if they wanted to keep a trail they could do that too. When user selects "Delete Violations as it was entered in error" the system deletes the record completely which is what everyone wanted.
After six months of use now I am asked to add an audit trail. I managed to do that also. I also looked at Browne's method but my data structure does not match the requirements for that method. I used an alternate method. It works as intended except when a record is completely deleted using the code I mentioned above. Then it goes in the infinite loop. I somehow need to bypass the before update event so that the function to write the audit trail is not called.
I am updating a value in an unbound control on an unbound form. When the value in the control is changed I want a sequence of code to execute. Specifically changing the record source of a subform and refreshing it.
The problem I have is that when the value of the unbound text box is control is changed (I am using a button to change the value as a test) the after update event on the text box does not do anything.
I did a simple test using a button to change the value in the text box and in the afterupdate event of the text box asked it to output the value of the textbox to a message box as shown below.
The problem is this does not work, nothing happens. If I tab out of the text box or change the value with the keyboard however the msgbox appears. Just not via a vba change.
Code: Private Sub Prod_ID_AfterUpdate() Dim pid As String pid = Me.Prod_ID MsgBox pid End Sub Private Sub Button_Test_Click() Me.Prod_ID.SetFocus Me.Prod_ID = "TEST" End Sub
frmTaskTracker -subfrmInbox (Datasheet View - based on a query)
frmUpdateInboxItem
subfrmInbox displays a summary of tasks on a task list. The user navigates to frmUpdateInboxItem from frmTaskTracker. After updating a record from frmUpdateInboxItem, it is possible that it the record in question will no longer meet the requirements to have it listed on subfrmInbox.
I have attempted to add code to the on close event of frmUpdateInboxItem to requery the sub form on frmTaskTracker but am not getting the syntax correct.
correct my code? Alternatively is there is a more correct way to do this, I'd be happy to learn it.
Code: Option Compare Database Private Sub cmdClose_Click() Me.[frmTasktracker]![subfrmInbox].Requery DoCmd.Close acForm, "frmUpdateInboxItem" End Sub
I have a customer table with all the usual customer details (customer ID, name, address etc...), a transaction table (Transaction ID, Order Number, Customer ID, Title ID, Date, Status (order received, payment taken, ready for dispatch etc...)), and i have a Title table (Title ID, Artist ID, Name, Record Label, Quantity, etc...)
what would be the easiest way to update the title table everytime a new transaction was added to tblTransaction so that the quantity field in tblTitle would go down by one for the right title?
Hi, I have a client who needs to build kits out items in his stock. So far I have a form where he can link the kit id to part id's. On the same form I have a text box which he can type in the kit id, then there is a list box which shows the parts linked to this kit, the list box shows the partid, part no and how many are in stock. I then have a text box which he can type in how many kits he wants to build. What I'm having trouble with is how to create an update query which will loop through the list box and adjust the stock to match whatever amount of kits he wants to build. So if he chooses to build one kit then one item will be added to the kit id and one will be taken away from all the parts in the list box linked to the kit id. I've searched this forum and can't quite find anything that will do this. Thanks
This code works well and frmRepair opens with the updated label caption. The original value was "Return/repair Information"
A few other things need to change on frmRepair depending on this caption as well as the values of some other fields, so I use the following code in the onload event (although I later tried the onopen even)
Code: 'Disable labels button if there is no RMA and the item is a repair MsgBox Me.lblmain.Caption If Me.lblmain.Caption = "Return/Repair Information" Then Me.txtRMA.SetFocus MsgBox Me.txtRMA.Text
[Code] ....
However, I cannot get this to work as the "if" statement always returns "Return/Repair information" and not the modified caption. The message box confirms that this is the case.
I suspect that this has to do with the point in time that the frmRepair loads or opens and when my code enters the modified values.
I have a simple data entry form on which there are about 30 to 40 data fields to be update by users. One of the field is called "Stage" which is a combo box with 2 entries "Formal Case" and "Informal Case" with no default value whien a form is first opened.
Users will normally create a data entry which updates the table and save.
However if in future users open any one existing record and try to change Stage field from Informal to Formal or vice a versa I would then like this action to
1) update a time stamp concatinated with the stage change value in a text field perhaps
2) save/close the form
3) open a new form with all the existing entries(plus changed status of "Stage Field") with a new record ID and new time stamp.
i've made an update query but its not exactly what i wanted is there a way to make the criteria equal a particular form field because i dont want the user to type in the stock ref in a parameter query.
also, how do i make it so no orders can be made on stock which has stock levels of 0 and have a message saying it is out of stock at the moment, i put >0 in the criteria underneath no in stock field in the update query
i hope i made it clear because im not sure how to explain it
I have a database that deals with rented movies. I want a means in Access to enable the stock values of the movie to be updated whenever there is an order for a certain film and whenever its returned. I am not sure if an update query or a macro is needed for this to work and how each would work.
I have a form which has a list of stock we have available to sell. I can then create an invoice and put in the relevant details. I then can select a product and it will add it to the invoice (which is in the form of a subform). Then the user confirms the quantity of each product. This is where the problems start....
I want an update query which will subtract the quantity from the overall stock level and give me the new stock level. This is easy to do if the invoice is for only 1 product. But I it doesn't work if there is more than one product in the invoice. If the first product has a quantity of 2 being bought, it will subtract 2 for every product.
When the user enters the quantity they require into the orders form I would like to deduct it from the quantityinstock field in the products table.
The above query does work if you add the following code to the beforeupdate event of the orders subform: If Me.NewRecord Then DoCmd.SetWarnings False DoCmd.openquery "qryupdateQuantityinStock" DoCmd. Setwarnings True End If
Although I wanted the query to run from a button (confirm order) on the order form so that the user decides when to deduct the stock.
I cannot put a button onto the orders subfrom where the query should run from and if I place the code on a button on the main orders form it only deducts one order (one product) and not all products on the order.
Can you help me find a way to run the query from a button?
I have read through the threads and found lots of information on stock control, although have not yet found anything similar to this problem. I understand that this is not the correct way to do stock control, although my database is for academic purposes and only requires a simple stock control system.
Any help would be greatly appreciated (sorry for the long post!). :)
We have our access database with a bit of a messy structure?
We use our database to record sizes of our product. I am hoping to improve it by adding the items we have in stock to prevent manufacturing more.
Basically our main database called "Make & Model 1" has a list of various makes and model numbers, each model number lists various information needed to manufacture a replacement part.
Customers order the part and these part details are entered into a table called "Order Detail" What I would like to happen is that when the details are entered a calculated field adds the data entered to a text string. I need to match the text string to the same text string in a table called "stock" as there could be one part that matches hundreds of models.
Basically the "profile" "Colour" "height" and "width" make the string and this is what I need to match and tick a box / populate the number of items in stock. Eventually I would like this to reduce by the amount ordered but lets do one step at a time.
I have a subform which makes a change to a field on the main form. When focus is returned to the main form, the BeforeUpdate and AfterUpdate events fire. Why? I thought from the form's perspective, the subform is just another control.
BTW, I get the same behavior if I modify the field from within the Exit event of the subform control.
In either case, the main form's Dirty event is NOT triggered.
I have a form with a subform. In the form's On Current event I have the subform's visible property set to false. There is a button that when clicked sets the subform's visible property to true. I want the subform to go to the first control in a new record which is a combo box. the subform is a multiple items form. I have tried the GoToControl in the subforms OnCurrent event, but it is not working. I get an error saying the database cant find a third form.
I have On Load, Current, After Update events on a form, which all work fine. But, when I add a Before Update, I get the error: "Procedure declaration does not match description of event or procedure having the same name". Following is my code:
Code:
Private Sub Form_Load() If InStr(Me.Filter, "=") > 0 Then If IsNumeric(Mid$(Me.Filter, InStr(Me.Filter, "=") + 1, Len(Me.Filter) - InStr(Me.Filter, "="))) Then Me.Tag = (Mid$(Me.Filter, InStr(Me.Filter, "=") + 1, Len(Me.Filter) - InStr(Me.Filter, "="))) End If End If
I want to call the "On Current" event of another form.
I have a parent(mainform) form and then I have a subform(CurrentForm) and in that subform(CurrentForm) I have another subform(mysubtab01). From the mySubTab01, I want to call the On Current of the parent form. How can i do this?
This is an example of how I am setting the record source of the deepest subform where I want to call the parent on current from:
Forms(mainform)(CurrentForm)(mySubTab01).Form.Reco rdSource = "Select * from subQrytab03"
I thank you in advance for considering this inquiry.
From within a Form.field (based on a master table query), I desire to trigger two events; one before update and one after update.
This function will serve to document specific form.field value changes to a "log" table for review prior to being committed back to the master table.
What I'm looking for are functions, which I can use in a Macro or VBA code to facilitate a field read and copy before change and a read and copy after change triggering.
I was wondering if you can call a textboxes onExit event from another form? On my form a user enters a search criteria into a text box and when they exit the textbox the results are displayed in a listbox. This form is a subform on my main form. I want to be able to call this onExit method from the main form. Is that possible? When I put in txtSearchBox_Exit, I got an error saying Sub or Function not defined.
I would like to make clickable labels on my form, so if the user has any questions about the field, clicking on the label would bring up a message box that would give them more information. I know how to write onclick events one by one for every label, but there are a lot of them, and I feel like there has to be a better way.
I put the label names, and the text I'd like for the message box in a table, and I'd like to have a module that will allow me to click on a label, and have the right text come up.
I've researched ways to do this and have come up with nothing. The farthest I've gotten is an array tied to the form open event that just displays all the message boxes from first to last, one after the other. I believe that's on the right track, but is not a workable solution as is. I need to be able to tie the message box to the actual label the user clicks.
