For entering ordered products there is a combo box for Product_ID which is displaying information about available products, their stock level, and price.
The Row source for Product_ID looks like this:
Code:
SELECT DISTINCTROW [qryStock4Orders].[Product_ID], [qryStock4Orders].[Product_name], [qryStock4Orders].[Available_quantity], [qryStock4Orders].[Selling_price] FROM qryStock4Orders ORDER BY [qryStock4Orders].[Product_name];
qryStock4Orders holds the calculated values of stock level (Available_quantity) for each product, grouped by Product_ID and Selling_Price. Selling_Price is from tblX.
Because I can buy one product from different suppliers (for different purchase prices) and purchase prices (so selling prices too) of the same product may vary over time, qryStock4Orders may list more then 1 available Selling_Price for that product, for example:
What I want to achieve is to turn this code placed on After Update event of Product_ID combo box
Code:
Dim strFilter As String
strFilter = "Product_ID = " & Me!Product_ID
Me!Product_Price = DLookup("Selling_Price", "tblX", strFilter)
in something usable, in order to save into tblOrderDetails the selected Selling_Price and not one random Selling_Price from the 3 available above.
As the stock level is updated only after Ordered_Quantity and Product_Price are entered manually, what I'm trying to do with the code above is to automate the data input into Product_Price field of tblOrderDetails.
Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.
However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.
Code: Private Sub Command26_Click() If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then Forms![test site]![number correct] = Forms![test site]![number correct] + 1 End If DoCmd.FindNext End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
I have a form called "Customer Details", on that form i have child forms, including one that has a schedule on.. so far everything seems fine, am able to get it to pull data correctly to the correct fields.. etc
I'm using 1 main table for all of this called "Cases"
Now the problem is, when i i click a button to print (this works so far), it opens another form and then fills in all the textbox's, however this is where i've got the problem, as it always seems to pull the data from the first record, not the record that i'm currently on..
so for example, if in the customer details, someone looks up "joe Blogs" using the navigation search and then wants to print it, by hitting the print button .. it should open another form, fill in the boxs and print "Joe Blogs", however it's always doing "Joe adams" - 1st Record
I have a problem with making a (simple!) search form work in Access 2000. I wrote the code in (what i believe to be) Access 2000 version, and it runs on my machine that is Access 2002-3. The search form uses VBA code; an if-then logic for 3 fields that are chosen by the user, and discounts fields that are not selected. The code then filters results from a query based on 2 tables and presents these in another form.
This all works fine on my pc, and on others using Access 2002-3, with macro security set at medium (and macros approved to run). However, the search form does not work on pc's running Access 2000. I do not know the security levels for these pc's but please if anyone knows of what may be causing this... please help!!
i have a form that pulls in a subform specific to the CurrentUser(). It is meant to be a shortcut or favorites form that holds 8 command buttons. I have Dlookups on the form to pull the correct fields from a table for OpenForm or OpenReport and then the name of the form or report they want to open. My froms work but when i try and open a report, it just prints the (correct) report and does not open it. I just want to open it.
Private Sub cmdBtn1_Click() If Me.btn1Action = "OpenForm" Then DoCmd.OpenForm Me.btn1Details, acNormal, , , acFormAdd, acWindowNormal Else If Me.btn1Action = "OpenReport" Then DoCmd.OpenReport Me.btn1Details, acViewNormal, , , acWindowNormal Else End If End If End Sub
In the following code to add an article to a purchase order line table and then open the form on the purchase order in question , I had to open the window "frmPurchase" twice in succession. If I omit the first docmd.openform statement, the form would open at the first record in the table, not the one defined in the where clause "PUOpen and PUCompID = " & Me.lstCompany.
I had noticed previously that, when the form "frmPurchase" was already open, Access went to the correct record, and not if the calling program had to open the window, which is why I came up with the idea of replicating this behavior in the code.
With rstC .FindLast "PULineNB <> 0 AND PuLineHeadingID = " & rstB!PUID If Not .NoMatch Then linenb = rstC!PULineNb Else linenb = 0
[Code] ...
Is this normal behavior, and if not, how can I improve the code?
I have a combobox on an unbound form. After making a selection, I click a command button which opens a bound form to a new record. What I need to code next or add into the macro, is for the selection of the combobox to fill in the contact id, and contact name in the correct text boxes.
The combobox row source is: SELECT qryPBS.ConID, qryPBS.PBSName FROM qryPBS; bound column 1.
I have a navigation form with a combo box. The combo box has a running list of manifest numbers. I select a manifest number, then click a button that brings up a new form with data related to the selected manifest.
However, I have to click and unclick the 'Toggle Filter' button on the new form to show the correct data each time I open the form.
I have a code to add links to files into a field, I recently migrated from Windows 7 to 8 and the name of the folders in 8 are different; therefore the database I have does not find the linked file anylonger.
To avoid this happening in future, I would like to have the option for the user to set the folder path through maybe a table and then the link will refer to the settings in the table.
Currently I am using the following code:
Private Sub AddLinkDoc_Click() Dim strFilter As String Dim strInputFileName As String Dim varDirectory As String varDirectory = "C:UsersMarco TagliaferriDocumentsBA CityflyerCrew SchedulerDocumentationDocs"
I have had some issues caused by users exiting the database entirely when they realise that they have entered something incorrectly; this in turn has caused problems re-opening the half finished record (the last step of the record creation is to allocate it a unique reference number, which is then used to re-open the record for editing on a point and click form) - now, this is entirely my fault for expecting users to clear up behind them or to exit properly.
So, I am making some changes - when the dashboard form opens it removes the database window close button. I have a separate close button which has the following code:
Code: Private Sub cmdGoodbye_Click() Do While Forms.Count > 0 DoCmd.CLOSE acForm, Forms(0).Name, acSaveNo Loop DoCmd.Quit End Sub
However, I would like to add a line to automatically save any records before closing each form - is this possible?
I have created Several Forms but I am not able to save any new records in them. When I click the add new button the records are added alright. but when I exit the form and reopen it all the new data added to the form is lost. I even tried the save button but that doesnt help. anyone has ideas what happening here..
I have an Orders form (frmOrders) which has a subform (frmOrderDetails) detailing the individual order items. For example, there is one order which may consist of many items.
The problem I have experienced is that if someone completes frmOrderDetails first and then moves to frmOrders the records are not saved correctly. I know this is because the main form frmOrders has not been saved or had a field completed which would produce an OrderID which can be linked to frmOrderDetails.
Whilst I could say to users they must complete a certain field first, some will forget and I would like to protect against this.
Whilst the tab order will go to a field on frmOrders, is there a way of forcing users to complete this field before they enter frmOrderDetails.
Alternatively, is there a way of coding a command button to initiatve an OrderID.
I was just wondering if it's possible to save a form with all its settings and selections, and reload it later? So if a user is filling a form out with a few subforms which show a yes/no field in datasheet view, if they select a few options in the yes/no field and hit save, when they reload, it will bring up the form with everything they had selected when they hit save. I'd like to put Save and Load buttons on my form if it's possible.
On a form there is a sub form.On the form I can only cycle through the records that have been created since the last time the form was opened. I cannot cycle through all records. Is this normal for a form. I assume some code might change this behavior.
More importantly when I look at the sub form in datasheet view I notice the first field of each record is not displayed (saved?) unless it is the very last record created. (This field has a SQL statement as a row source). Is this normal - or are the last 2 paragraphs descriptions of the same thing?
Below is the code for exiting without saving. It works perfectly fine. I just want to know if I can change the MSGBOX message to something like "Are you sure you want to exit without saving?" instead of its default message - "Are you sure you want to delete this record? and also, right now, if I click NO when I am asked if I want to delete this record, it exit the form rather than staying in the same form. But how do I make it to STAY in the same form if I click NO.
Code: Private Sub Command24_Click() On Error GoTo Command24_Click_Err On Error Resume Next DoCmd.RunCommand acCmdUndo
I have one problem with subforms. I have one bound main form and two bound subforms: one subform is used to display existing records in datasheet view while another is used to enter new records in form view, but this latter subform for new records will not save automatically, nor will it save by using the me.refresh or me.dirty=true in the unload event of the subform control. The parent child relationship is intact. I can even see the primary key of the new row in the subform but it does not appear in the table that the subform is based on. This primary key adds by one automatically.
I have struggled and finally made work a combo box in the main form that finds existing records but also is able to add new records when the value entered is not on the list. Below is the code I used. I am not sure if this code is overriding Access's save ability of the subform?
Private Sub Combo316_AfterUpdate() Dim n As String gblvariable = cbobox Me.Requery
I have created a form with data pulled from tables and queries, I need a way to create a submit button and have it saved to a table I have created. and also clear the data from the form, also to give a warning if not all fields have been filled in. the Form is frmTM and the table that I need it sent to is Team Member Data.
I have a field on a form [vin] that is based on a table, except i changed control source to a Dlookup to pull the vin number in from another table so i wont have to type it when i enter the record. However, this field is not saving when i save the record. I cannot just make a relationship because we reuse the same vehicle numbers, when we sell van 1 with vin 123, and replace it with a new van with Vin 456, we still call it van 1, so the unique vin is the only way to distinguish the two.
I have tables, forms, and (will be) many data. But many data means that the file will be heavy (e.g. need a longer time to open it) and since it's located in server, meant that it'll also burden the servers when someone is accessing it. So, the idea is to make an access file consisted only of forms (and maybe tables), and another access file as the data-bank. And everytime the form is filled, it'll save the new data to the data-bank. So, it will be easier for the end-users to acess the form (e.g. saving time when opening the file).
My question is:
1) Is it really lessening the burden on server?
Because I think that even if the end-user only accessing the form, the form itself will be accessing the bank-data, and I do not see how it'll lessen all the process.
2) How to save to another database?
I used this command on VB:
Code: Set db = CurrentDb
Can it be used to save the form's data to another access database? How?
Hi i was wondering if anybody could offer some help to a problem i don't know how to solve. Some Background; i have two tables that are indentical called 'Order' and 'TempOrder', the purpose of this is that one saves the records unless manually deleted later, whilst the other is designed to have its own form, which has a print button which also deletes the record. Now i can create the data entry form to save the details in order, however i also need it to save the details into temporder, which i just cant seem to do, as i do not know the code, it appears relatively simple just i cannot seem to do it, i have tried a macro to save the record into the 'tempOrder' table but this does not work, any help would be much apprieciated.
I am new to Access and I have get to where the users can make selections in the listboxes, but then I need to save the selection in a query or table to save thier changes.
I have a form frmAddNewProject that is a Data Entry form.When you click Add Project on the form, it creates folders and copies files to a location and also creates new record(s) in the ProjectT table with appropriate information.Here is my code:
Code:
Private Sub cmdAdd_Click() Dim FSO As Object Dim FromPath As String Dim ToPath As String Dim FolderPath As String Dim strType As String Dim strYear As String Dim strGPN As String
[code]....
Everything works fine till here. Now I would like to close the form but closing it gives me an additional empty record.
Code:
Dim ctl As Control On Error Resume Next For Each ctl In Me.Controls ctl = ctl.DefaultValue Next Set ctl = Nothing
to remove all values, but then it just adds an empty record.Is there a way to close that form without removing my legitimate new records and without adding empty ones?