Hi,
I have a check box that gets ticked when 'work complete', it adds the parts/service totals and fills in the 'parts total', 'vat' and 'total' fields.
This works fine but I need it to write the values to the table as well rather than just displaying the values in the fields so reports can be produced etc.
Anyone know the easiest way to do this?
Thanks
Housey
Hi,
I am fairly new to msaccess programming and am trying to build a stock control system for my business.
I am happy with everything I have done so far but there is one thing I cannot figure out.
I have set up so that the user can click a button and remove the relevant number from the stock level. The buttons are for removing 1, 5, 10 or 20 (there are reasons for these numbers). However I would also like the button to take information from the form (which is referenced to a query) and write that information, along with an autogenerated id number and the current date/time to another table. This is for our record keeping, so that we can record what is selling well etc and when the last one was sold.
How do I do this?
I have gone through the help files and my books, but I think the problem is I don't know where to start looking.
I also want to add a button that will add items to an order table. Again by taking the information from the form and writing it to another table. With this one a message will appear asking for the quantity to be entered. It will also check the order table to see if the item is already there and advise if it is.
Any help gratefully received.
Steve:confused:
I hope this makes sense?
I have a main form with a button that opens a new form. On the new form I have 5 fields. 1 of those fields is the Employee ID which I have the form getting from the main form (IE the Control Source is =Forms!Search_Employee!Employee_ID )and the other fields are bound to my table for the new form. I am unable to get the Employee ID to show in my table, it is not updating or writing the value to that field. Not sure if there is code or how I should or can get this field to write to my table. Any Ideas or be very appreciated. Thanks anyways guys I igured this one out. I was able to set the dafault value to point ot my main form so that my Control Source could point to my table and it worked.
Hi all,
wonder if anyone can assist me with this the SQL Server
security/write (?) issue. The SQL Server Admin has created a table for our Dept. to use and via SQL Server I can get access to it using the username and password she supplied to me. I can also link to this table in Access 2002 via ODBC which is the way I am using to write to SQL server tables. I have developed an Access application to allow one of our staff to be able to write data to the SQL Server table at a click of a button, however when I open the table the 'add new record' button is greyed out, I can not add any data to the table.
I explained this problem to the Admin as well as the way in which we access the table and she is certain that the problem lies in Access but i'm unsure how that can be the case.
Has anyone experienced this problem before ? Is there a fix and if so how is that implemented ?
Should I also post this in the SQL server forum ?
Thanks in advance,
Mitch....
Hi all,
I need to figure out how to have two calculated fields written to the database table instead of just being displayed in the form.
The user selects a number from 1 to 5 in the first field, and a number from 1 to 7 in the second field. The first calculating field multiplies these two numbers and displays the result. The second calculating field determines where the resulting number fits in three ranges of numbers and displays the result as Low, Medium, Or High.
My problem is the calculated results are be displayed in the form but not being written to the table. I cannot create a report to display all the High results, etc.
How do I update the calculation field formula to write the results into the table so I can create queries/reports on the calculated data?
Thanks,
bugleboy62
I'm using Access 2010 and am trying to pass a variable in a form field and store it in a table i.e. I have a customer database with various fields: Customer_ID, Customer_Name, Customer_Address etc. After selecting a particular record i.e. Customer_ID = 20 l need to store the record number '20' in a temporary table. I presume that l could then use Dlookup to read the info back at a later stage.
View 7 Replies View RelatedCan anyone tell me what's wrong with the below statement ? I'm trying to pull the contents from a text box in a form. The text box is supposed write the contents to a field (memo data type) in a table. Thanks ! ! !
strComments = Forms![Payroll History]![Comments].Column(0)
Hi Guys, I don't know Access very well but I know enough to generate a 2 dimensional database. The problem I have is that I have been asked to look at a travel agents database to create some additional letters. The problem is that they use a form which has an underlying data table called customers. When they create a record in the form all fields in the customers table are filled in except those on the form which derive their data by calculating two or more fields i.e Date Due is [Date of Travel]-70 (days), Balance Owing is [Cost]-[Deposit]. None of these derived fields fill in their corresponding fields in the customers data sheet.
I would welcome any ideas please. Thanks in anticipation
PompeyFC
I am trying to write some fields to an access table definition. It is working for the most part except that I want to write some of the fields as dbText and others as dbDate. For some reason it is writing all of them as dbDate and I don't know why.
Code:
additionalColumns = Array("a","b","c","d","e","f")
'add additonal columns to report
For i = LBound(additionalColumns) To UBound(additionalColumns)
MsgBox additionalColumns(i)
If additionalColumns(i).Value = "a" OR "b" Then
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbDate, 10)
tb1.Fields.Append FieldName
Else
Set columnNames = xlsht.Cells(1, additionalColumns(i))
Set FieldName = tb1.CreateField(additionalColumns(i), dbText, 150)
tb1.Fields.Append FieldName
End If
Next i
I need to create a table with names of controls on my forms.
I'll use that table to apply user rules.
Any solution to write the table automatically using VBA code ?
I know how to populate a combobox...
DoCmd.OpenForm YourFormName, acNormal, , , , acHidden
Dim c As Control
For Each c In YourFormName.Controls
[Control-Name].RowSource = [Control-Name].RowSource & c.Name & ";"
Next c
..., but not how to write a table.
I concatenated 2 fields CR_Numbers: = CR_Number & [Sub Number] to display 12.01: from 12 and 1. I am now wanting to lookup the CR_Numbers to edit or add information into other fields.
I want to use a pop up form to edit/add data without having the form add an entire row to the table. How do I reference the Table ID so that it will go to the equivalent row to add the data in?
Main Table = Change Request
Email Table = AORB_Email
Fields in Form!AORB_EMail: Change Request (CR_Numbers), Priority (Pri_Name), Hours (Hour)
I plan on using the Priority and Hours in the email to set some dates and expiration times as well as in the subject of the email. I have figured out the email setup, and if I manually fill in the Priority and Hours fields everything works as it should.
This is the first time that I have done any major work with forms. After I thought I had finished a problem came up. There are several forms that are use to input information into a table. When the form is opened it grabs an automated number for tracking. The problem is, that if the form is opened and then closed it creates a line of data with all null values.
What I would like to do is have the form open, the user fills out the information, and upon pressing the "save" button, the data will save to the table. I think I need to have each text box write to a variable and then save on the click event. Or I could be completely wrong and need to do something else.
i'm guessing this is something I would learn in Forms 101 if i had ever taken that type of course
Thanks
I am using Access 2010. How do I prevent the object typed into the textbox on a queryform being written to the table. The result from the name typed into the textbox on my query form correctly produces the result from the query, and my macros then produce the correct report, which I can either print or close due to the controls in the heading of the report. However, when I view the table, the name typed (only) has been inserted into the correct field as a new record in the table. Is there a macro I can add (I assume to an event in the query form) to prevent this happening?
View 8 Replies View RelatedBasically, I originally wanted to create a form which I can input data into Access with by using a button (I was unable to get Access to append the information from the forms to the table, so it didn't work out!). I did not want the fields on the form to be linked to a table, as then a record (and most importantly an auto-number) is created as soon as somebody starts typing. Should somebody stop typing halfway through and quit the form an autonumber will have been generated, which makes the number of "users" seem higher than it actually is. I got around this by changing the field from autonumber to number and then creating a query that selects the maximum value of ID in the table, then adds 1 to it (which is essentially the lowest unique number. I tend to call this newID).
The problem I now have is setting this to be written to the table alongside the data from the form (the rest of the fields on the form are now connected to the table, as I am no longer using an autonumber). I have tried the following: Setting the form to run the expression "[ID] = [qryMaximumUserID]![NewID] " on load. This returns the error "The object doesn't contain the Automation object 'qryMaximumUserID.' Setting the control source of the text box to be dLookup. This fills the textbox with the correct value, but then it doesn't write it to the table! Setting the default value of the field to be 0, then running an update query to update any ID of 0 (criteria "0" to the value of newID "update to: [qryMaximumUserID].[NewID]. "). However this doesn't work as whenever the query is run it asks for a parameter to be entered, rather than just taking the value from the other query. Writing a macro that is run on load to SetValue of item:[ID] to expression: [Forms]![qryMaximumUserID]![NewID]. However this returns an error "Microsoft Access cannot find the referenced form 'qryMaximumUserID' you entered in the expression.
I am periodically importing Excel files into access.Making the data usable requires removing spaces, parsing certain fields, adding datasource field, etc. Currently, I am importing the un-formatted data into a staging table, cleaning it up with a query and then copying the updated staging table to the final table.
View 2 Replies View RelatedI'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
There is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.
How can I do that ? :confused:
Thanks
I have a table (Table 1 let's say) which contains a list of numbers that all have unique ID numbers in another table (Table 2). Is there a way to create a column in Table 1 that will link those unique ID numbers from Table 2 to their corresponding values in Table 1?
What happened was I imported a list from Excel and there are frame numbers which are unique (but I still gave them an ID) and I now realize that I have another table which lists all these products, which all contain this frame number. I would like to replace the frame number with the correct ID number.
I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.
This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.
How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies View RelatedHi
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
Help me in writing this code.
Thanks in Advance Very much
Deepak
Hi gang, I'm an Access rookie with a problem. I have a table with three columns and I need to create a result field which will "count" the number of occurences of a value in the "CustNum" field. (There are other columns in the table as well so I can't use a "count" function) Please see my simulated results in the "Count" column. I'd like to have a query create this expression. Any help? Thanks! Sorry for the formatting, not sure how to replicate a table in my post.
CustNum FirstLast Count
9891247372KURTFENCHEL 1
9891247372KURTFENCHEL 2
9891247412GRIEGMOORE 1
9891247412GRIEGMOORE 2
9891247412GRIEGMOORE 3
9891247414JohnVerwey 1
161243818DANILOLAMBO 1
9891247198MICHAELSAUNDERS 1
9891247211CATHLEEN DELANOY 1
9891247211CATHLEEN DELANOY 2
9891247211CATHLEEN DELANOY 3
9891247211CATHLEEN DELANOY 4
9891247211CATHLEEN DELANOY 5
I'm not sure this can be done,but here goes it, I have 5 [plants] A, B, C, D & E. If one plant recieved [A] plant reject [Reject] then I need a response from plants B, C, D & E. If plant [B] receives a reject then I need a response from plant A, C, D and E. How would I show what other plants havent' responded>
How would I create a query for this? any idea's would be greatly appreciated
Hello,
I have a procedure which I undertake and wonder whether it can be automated in any way.
I have a field on a form for Purchases (frmPurchases) for a Purchase Order number. To get the order, I click on a command (cmdpo) which opens another form and clicking a command on this form (cmdgetpono) produces a unique Purchase Order number. I then manually copy the number given and paste it into the field on frmPurchases (PONo).
I have not used Macros before but cannot see that there are the options to achieve this. If someone could suggest the ones I should uses fro the list it would be appreciates.
Alternatively, is there another way of looking at this?
Thank you
Lin