Tables :: Modifying MS Access Database - Direct Order Or Placement Of Fields Within Table

Feb 5, 2013

Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?

I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.

For example - if TableOne has 3 fields - Field10, Field20 and Field30.

I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.

I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.

View Replies


ADVERTISEMENT

Direct Table Access

Feb 23, 2007

I have a database that's almost finished. I've built a few forms for the major parts of the database, but some other data is only changable through the tables directly. The remaining tables (without corresponding forms) are pretty small and shouldn't require changing that often.

Assuming I put user-level restrictions on the database, would it be okay to allow certain users to directly add/edit/remove entries to these tables?

My instincts tell me I should make a simple form for each table, but I'm looking for the path of least effort.

View 3 Replies View Related

Forms :: Access 2002 - Modifying Properties Of Subform Fields

Feb 2, 2014

I have a Form with embedded Subform, everything works well as far as moving through records. Scrolling through records on main table via Form brings up correct records of child table on Subform, so the link between the 2 tables is done properly.

I use this logic to lock/unlock a field in the subform.

Code:
Private Sub Form_Current()
If IsNull(Me.Parent!Spec2) Then
Me!Value2.BorderStyle = Transparent
Me!Value2.SpecialEffect = Flat
Me!Value2.BackStyle = Transparent

[Code] ...

I confirmed with debug that the logic does set the properties properly. They are updated once and that's that. Any further passes through the logic do not update on the form. They do pass through correctly, changes are just ignored.

It doesn't matter if I use the logic from the Form and refer down, or in the Subform and refer up, the Subform never gets updated. I tried Repaint, Refresh, Requery, nothing works. I cannot count the permutations I've tried.

View 9 Replies View Related

Tables :: Modifying Data Using Another Table

Aug 19, 2014

I have a table that has a field that holds a total. Through historic reasons the total in a (relatively) small number of cases is wrong. I have analysed and worked out what the difference should be and have created a table with the value that is missing. I basically want to add the 2 together.

So I have

Table1 Field1 Value1
Table2 Field2 Value2

I want table 1 to be

Table1 Field1 (Value1+Value2)

Programming wise I would just have
Value1 = Value1 + Value2

How do I go about doing this in a query or some other whizzy way?

View 2 Replies View Related

Access 2010 - Modifying Forms In Split Database

May 11, 2012

I have a access 2010 split database and i need to modify one form on the front end (it is an accde file). However, when i open the Front end, i cannot find the possibility to go into the form design mode.

How do i need to do it? do i need to restart from the unsplit database?

The both the front end and the back end are password protected.

View 1 Replies View Related

Tables :: Modifying Table Structure - How To Provide Multiple Names At One Address

Jan 8, 2013

I need to modify my customer table to cater for multiple names at one address.

Should I move all the addresses into their own separate table or leave them as is (name-Address-postcode- email etc in one table) and put the additional addressees into a separate table or is there a better way I have not thought of.

View 6 Replies View Related

Combo Direct From Table

Dec 7, 2005

I want to create a combo box that links direct to a table, rather than going through a query. Then when you select the record from the list it updates a text field to show the memo field of which the selected reccord is related

Can any one help

I tried using a query to do it, however queries only ever allow a maximum of 255 charcacters in the fields
basically the code looked like this

cboAfterUpdate()

me.txtfield = me.cboselection.column(1) ' because the data was in the seccond column
end sub

but as you can guess this only allows 255 characters to come accross from the memo field

View 1 Replies View Related

Reports :: Excel Template File - Direct Access What To Put In Which Cell?

Nov 24, 2014

How much formatting can be done to a report from Access into Excel? I am trying to (or will be in the next day or so) to create a report to export data and I would like it presented in a specific format. This is hopefully to replace a spreadsheet where someone currently has to collate and re-type a load of info that has already been typed into various other spreadsheets.

Access is going to happily cope with all of those users entering their data to a table, and I would like to be able to output that data to something similar to the end result now? Is there a way to have an Excel template file and simply direct Access what to put in which cell?

View 14 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

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 Related

Modifying Database From Excel File

Nov 4, 2004

I have a a database with a large number of email addresses in it.
I also have a list of email addresses stored in an excel file.

I would like to take the excel file and use it to check for the same email addresses in the access database and if they are present in the database add content to another field in the database to flag it.

Can anyone help?

Thanks.

View 4 Replies View Related

General :: Calculated Fields - Reading And Modifying Records

Aug 2, 2012

I am new to Access and this is likely a very elementary question. I recently created a database to track status of projects including progress and funding. The table contains several fields that record various aspects of funding. These fields are added (very simply) and create a calculated field that is a summation of those fields. The table currently contains approx 60 records. The calculated field functions for 2 or 3 records and will not calculate further. I have tried this in a form as well as a query. The form will change the same number, 2 or 3 records. When the query is run the result shows only the 2 or 3 records. What I am doing that is only reading and modifying those records?

View 3 Replies View Related

General :: Prevent Users From Modifying Database

Aug 6, 2013

Is there away in which I can protect an access 2007 database modification with a password.I'm doing this because I would like to prevent my client from modifying or having acces to the database's back end system (db), I would just like him to use forms and nothing else.

View 3 Replies View Related

Tables :: Auto-Fill Fields Based On Linked Table (Access 2007)

Jan 6, 2015

I understand right off the bat if you're reaction is "don't duplicate data!!" -- mine would be too (don't fret, I know my normalization).

I've linked a table in my db to my Global Address Book in Outlook 2007 and, upon entering an employee number as a new record, would like to verify that the number entered is listed in the GAL and then pull in the associated name and location info.

The key is that I don't want this info to rely on the GAL going forward. For example, if an employee leaves or is no longer listed in the GAL, I don't want to lose the employee info (past data is needed for audit purposes). Note: I will be creating a report later to show if there are discrepancies between the GAL and my table, but that's another story...

So, what would be the best auto-fill options in Access 2007?

View 3 Replies View Related

Tables :: Autonumber Order In Existing Table

Jul 9, 2015

I was adding an autonumber field to an existing table and I assumed the numbering would follow the order of the primary key but that doesn't seem to be the case.

View 9 Replies View Related

Modules & VBA :: Database For Quotations - Modifying Sequential Numbers

May 29, 2015

I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.

This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?

QuotationNumberCode.PNG

View 10 Replies View Related

General :: Order Database - Discount Table With Percentage For Each Item

Aug 23, 2013

I have been asked to create a database that creates orders, debits stock etc for my company. Now I have been asked to put in some kind of discount table with percentages for each individual item for each individual customer. What would the best way of doing this?

I figured in the Customer Table I would have a code such as 'CustomerADiscount' and 'CustomerBDiscount' etc. and then in my table maybe

Product A - CustomerADiscount - 10%
Product A - CustomerBDiscount - 20%

Product B - CustomerADiscount - 5%
Product B - CustomerBDiscount - 7%

This link these values up within a query? Would this be the best way of doing it?

Is Access able to determine a percentage in "10%" or "5%" or do I need some type of formula for it instead?

View 3 Replies View Related

Tables :: Generate New Order ID For Same Customer In Orders Table?

Oct 3, 2012

how do i get form to generate a new order ID for the same customer in my orders table

View 2 Replies View Related

Code Placement For A Graph

May 7, 2006

My problems is a difficult one.

Here is the breakdown. I have a number of tables within a database, I have a main screen as to pick the required table I want to display in either tabular or graph format, the table works. I have created a graph and am trying to get the code or row source in the properties to look at the VBA I have written.

Any ideas please?

It’s something to do with the row source type. If it were a function, I would just put the function name there. It’s a private sub.

Private Sub ListMonkey()

Dim TableName As String
Dim strSQL As String

TableName = Me.Graph_Selected_Table
strSQL = "SELECT [LOAD], [Displacement] FROM " & TableName

End Sub

This is the last little thing, and I have no idea how to solve it.

View 1 Replies View Related

Form Items Placement

Nov 6, 2006

Suppose you have 10 fields on a certain spot in a form. And you want to place these ten controls, say command buttons) to line up under each other with an even spacing of about 0.2...Up to now, for a whole lot of controls, I've been selecting them one by one (or "line" by "line"), and manually entering the correct numbers in the left and top property setting fields so they line up the way I want them to. In the beginning I've tried selecting multiple controls at once and then using align but this actually never does what I want it to do.Do you guys and gals know something I don't concerning this which could make life easier?

View 2 Replies View Related

General :: How To Make A Report Using Crystal Report Direct From MS Access Form

Jun 3, 2014

I'v looking for since a couple months a go to make a report direct from access form using crystal report but i havent found it yet. I'v tried this code and its giving me errors. " run time error 1004 method range of object _global failed "

how to make a report using crystal report direct from ms access as front end application ? is it possible to use crystal report ?btw i use database sql server 2008 and MS Access 2007 as my frontend application.here's the code that i'v found and gives me an error

Dim CR As New CRAXDRT.Application
Dim rep As CRAXDRT.Report
Set rep = CR.OpenReport(Range(" ??? ")) * i getting error in this line, what should i do to fill it ??
rep.ParameterFields(1).AddCurrentValue "Boston"
rep.ParameterFields(2).AddCurrentValue "Cars"
rep.Database.Tables(1).SetLogOnInfo "tool", "db_tsel"
rep.ReadRecords
rep.PrintOut promptUser:=False, numberOfCopy:=1 ' promptUser:=True doesn't work

View 2 Replies View Related

Modifying Access Graph To Create Bins

Nov 22, 2013

I have been struggling with modifying a graph that I have made into a counting graph, I have wasted over 4 hours trying to do this modification unsuccessfully.

I am attaching 4 pictures, The Depth_Ranges Table Visual is the query which creates graph 1 you can also see in the additional attached picture of the design view the code that creates this graph 1.

Now I need to add a field in the Design view of that query in order to have a result of the graph 2 that you see in the picture.

Basically, creating counter that will count how many values are in each bin labels that you can see on the picture of graph 2.

View 5 Replies View Related

Modifying Access Pass Through Query Using Querydefs

Feb 8, 2012

I have a pass through query which I want to add a parameter to the end.

The pass through query is very long and has to crate temporary tables on SQL Server in order to generate the required output.

I do not want to retype or try and paste all of this code into a vba module.

Can I use querydefs or something in the querydefs collection to extract the code into a new query and append my parameter along the way?

A simple way of looking at this could be

querytemplate:-

"set nocount off

Select UWREF
from policy
where UWREF like '"

parameter:- (picked up from a text box on a form)
"ABC123"

additional text to follow:-
"%' set nocount on"

In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.

View 1 Replies View Related

Tables :: Calculated Fields From Two Tables / Based On Relationship In Third Table

May 29, 2014

I have two tables of data, each relating to three business branches (branches A, B and C).

Table 1 shows the expenditure of each branch (by fuel, premises and wages).

Table 2 shows a number of units for each branch (mileage, floorspace and sales).

What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.

View 6 Replies View Related

Tables :: Auto Populate Fields In A Database

Dec 6, 2014

I have a table called Jobslog which contains records for my permanent jobs I have to do for customers in one week.Structure is like:

JobID
TeamID
DayName
JobDescription
Instructions

Each job is assigned to a team, but is following same schedule every week.Can I create like a module or query to autopopulate the table with next week jobs, but putting the date as well, not only day name.For example, each Monday job should have a date field which should be 8/12/2014, Tuesday jobs should have 9/12/2014 and so on until Sunday.Usualy they are more jobs for each day (not day and job).

View 2 Replies View Related

Tables :: Joining Two Tables In 3rd Table With All Fields From Both

Oct 27, 2014

I have a db with 2 tables one with 10 fields and the other with 11 fields. In these tables there are 4 and 5 non common fields in tables respectively.

How can I join these tables in a 3rd table with all the fields from both tables.?

View 1 Replies View Related

How To Access Ms-Access WorkGroup Tables To Change Database Login Password

Apr 13, 2007

Hi!
This is Kishore, working on VB Project which is using MS-Access95 as backend.
Now, i want to change the Database login Password.
Could anyone guide me in this context.

Regards,
Kishore

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved