Delete Fields Referring To Field Number

Aug 5, 2006

I need to trim a lot of tables. Is it possible to delete table fields with reference to the field number instead of field name?

The command 'tblMyTable.Fields.Delete ("FieldName")' takes a string (field name) as input. I rather like to - in a loop - delete say,

Sub DeleteFields()
For i = 40 To 60
tblMyTable.Fields.Delete Field(i) ' Not correct syntax
Next i
End Sub

thh

View Replies


ADVERTISEMENT

Referring To Form By Using Its Index Number

Aug 23, 2006

How can I refer to a form by using its index number? Also how can I retrieve an index number of a form?

(I'm opening multiple instances of the same form and read that the only way to refer to those instances is by using their index number. I found some solutions that use hWnd as well but I can't get any further support on it as far as referring to a form by using the window handle so I'm trying to capture and then use the index number)

View 4 Replies View Related

Number Fields In Text To Number Field, Formula For Fiscal Year

Nov 26, 2004

Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?

There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.

Thank you

View 6 Replies View Related

General :: Referring To A Field That Is Not In A Form

Dec 3, 2012

I would like to know if it is possible to refer to a field even tho it is not in a form, i.e.,

PHP Code:

strSql = "UPDATE tblItems " & _ "SET StockQTY = ([StockQTY]+1) " & _ "WHERE ItemsID = " & [ItemsID] & "" 

The [ItemsID] is actually on the form as a txt box. but i dont really need it there for any other purpose other than the vba above.

In the form i have a field that is in the form as a txt box that has a relationship to the ItemsID. i have attached a image of the relationship.

The field that is in the form is tblOrdersItems.OrdersItemsID and i also have tblItems.Items. so rather than adding another meaningless txt box to the form(ItemsID) i would like to be able to use the relationships to get the correct ItemID ...

View 1 Replies View Related

General :: Combobox Not Referring To Correct Field

Oct 22, 2014

I have a form which contains fields from my main table and also has a subform containing a query based on a filtered list of my main table.

I have a combobox on the form to select a name and pass it to the query to filter on.

The list of names is in a table called tblnames which has 2 fields, name and ID. I have linked the ID field on this table to a field called nameid on my main table which is a numeric field (and that allows be to select a name from a list when I enter data into my main table.

The xox is unbound, control source empty and row source set to tblname.

The combobox is only showing a list of numbers (I assume they are the id field from tblname). Yesterday I had the list of names showing and I checked a backup and the only difference I can see is the row source property refers to the name field in tblname but I don't know how I got it there. When I click on the list box for row source I only get offered a list of my objects.

View 10 Replies View Related

Modules & VBA :: Referring To Calculated Field In A Recordset

Nov 22, 2014

I am trying to use a calculated field in a recordset but I am having problem with the script trying to compile.

my record set is

Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price, Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)

The calculated filed I have added in is Round(Price/7,2) AS Daily_rate

If I let it run just with this it runs fine.

and then I try to use this calculated field:

Do Until rstPrice.EOF
Temp = Temp & rstHTML!html35 & rstPrice!Period_desc & rstHTML!html36 & rstPrice![Price] & "-" & rstPrice![Daily_rate] & rstHTML!html37
rstPrice.MoveNext
Loop
rstPrice.Close ' Tidy up
Set rstPrice = Nothing

They it stops running and it all seems to be because of the Daily_rate field

View 11 Replies View Related

Reports :: Report Keeps Referring To Wrong Field?

Sep 30, 2013

I have a query[view] where I have six employee numbers, I left join these to an Employee table to retreive the names.

I've created a report that uses this query/view as its source. For some reason my report keeps making all six name fields refer to the same control source Employee.Name and not Employee_1.Name, Employee_2.Name, Employee_3.Name,.... respectively.

In design view everything looks fine but when I switch to report view the switch happens, it displays the same name in all fields. When I switch back to design view all of the fields have changed to the same control source.

why this is happening? My tables are in a SQL server.

View 10 Replies View Related

Tables :: Running If Statement On Records - Referring To Table Field

Apr 19, 2013

I'm trying to run an if statment, on the records in a field (called "Current_Month"), in an existing table called ""Current_Months_Lag1_Data".

The IF statements work fine - and simply perform a different action for different data in "Current_Month".

I am having problems referring to "Current_Month", where I keep getting 424 (Object required) and 3420 (Object invalid) error messages, on the last line of code.

I have defined & identified the table in which the field is located, yet somehow cannot identify the field within that table. (I've already tried searching the web for similar problems under error messages 424 & 3420).

Function LAG_Forecast_03()
Dim dbs As Database
Set dbs = CurrentDb
Dim Tbl As TableDef
Set Tbl = CurrentDb.TableDefs("Current_Months_Lag1_Data")
Dim FLD As Field
Set FLD = Tbl![Current_Month]

View 6 Replies View Related

General :: VBA Error Referring Control Field In Subform Using Main Form

Feb 22, 2013

I believe I did this before awhile ago but for some reason I keep getting an error.

I have a Mainform (frmMain) that has a Subform (frmSub). On frmSub I have 2 comboxes (strCom1 & strCom2) one is set to invisible (strCom2.Visible = No).

So using the "On Open Event" of frmMain I want make strCom2 visible if strCom1 = "Read Only". Ofcourse I will also need to place the vba on the On After Update event. Below is what I have so far but doesnt work.

I get Run-time Error 2427 "You enetered an experssion that has no value"

Code:

Private Sub Form_Open(Cancel As Integer)
If Me!frmSub.Form!strCom1.Value = "Read Only" Then
Me!frmSub.Form!strCom2.Visible = True
Else
Me!frmSub.Form!strCom2.Visible = False
End If
End Sub

View 5 Replies View Related

Converting Text Field To Number Without #Error In Empty Fields

Apr 6, 2006

I have a text field I need to convert to numbers. There are both empty and non empty fields. I then need to show the numbers like this "1.234" and a "*" in the empty fields.

I have tried to convert the fields with Cint, but I get an #Error in the empty fields.

Is there any way to solve this??

Thanks in advance..

View 6 Replies View Related

Queries :: Extract Number Out Of Field And Update Extracted Number To Another Field

Feb 6, 2015

791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.

View 1 Replies View Related

After Delete Any Record, Autonumber Start With Next Number

Jan 16, 2008

I create autonumber as primary key in a table, then i tried to enter a record in that table. Then i deleted it, but when i enter a new record, auto number started with the next number. Ex: i enter a record and autonumber for it is 1. I deleted this record and entered a new record, but i found autonumber is 2. I want the number is 1,

Why is it happen? any solution ? help me pls...

Thanks

View 14 Replies View Related

Delete Fields From Table

Aug 30, 2005

Hi

Is there a automatic way i can delete Fields from a Table? This table gets input into the database very frequently as updates come in and i'd like something like a Query or Macro, where i can define which fields i dont want, to delete the 30+ columns of useless data.

Can't see how to do it with a Query or macro though

Anyone have any ideas?

Thanks

View 5 Replies View Related

Delete Extra Fields

May 28, 2006

Hi
Does anyone know how to delete the extra blank fields you get at the end of a query. I am converting the query to a handheld database with a field limit and the extra fields push my field count over the limit. i have tried just 'deleting' them but they reapear when I reopen the query. they definitely count as fields because if I delete some real fields i can do the conversion. i dont want to alter the structure of the original database as it has been in use some time and works well. any ideas advice
Thanks
supateach

Hi again
I am still having problems with too many fields in my query so it will not convert to a handheld database. I have tried making a test query (with the wizard)from the same table and sure enough there are extra fields at the end, which do not come from the table, which reappear after they have been deleted, when you reopen the query. I have attatched the query to illustrate that I have deleted the fields and then they reappear. It does appear to be impossible to get rid of them, in which case I shall have to decrease the number of 'real' fields to get below the max the handheld can convert. Any advice gratefully received
Thanks
supateach

View 1 Replies View Related

Automatically Delete Data From Certain Fields

May 23, 2005

I have a table in my Database that uses the feilds Location and partnum. I use a barcode scanner to generate each of these fields. The problem I am having is that when our part number is scanned it contains data such as "des--. 123456 --aa". I have been doing a find/replace on this data to remove the "des--." and "--aa" but I was wondering if access has a function to do this for me so that I can get a macro to check automatically for this type of problem. Any ideas?

View 1 Replies View Related

Delete Fields In Tables By Using Sql Statement

Nov 14, 2012

I need to delete data in a table that named Cap One Table. I can not use a delete query since database located on company server and it is being used by many people. So, I have created a form and put on there two text boxes - txtStartDate and txtEndDate and command button cmdDelete. An idea behind this is that a user types date in txtStartDate and txtEndDate and click command button and delition happend in table Cap One based on that criteria. I put the below code in command button click event , but it doesn't work.

Dim startDate As String
Dim EndDate As String
Dim strSql As String
startDate = txtStartDate.Value
EndDate = txtEndtDate.Value

[code]....

View 4 Replies View Related

Delete Record If 2 Fields Are Blank

Jun 20, 2012

How can I create a criteria to delete the entire record in a query if the LnPreApprConvDt and LnPreApprFg are both blank?

View 1 Replies View Related

Reports :: Generate Number Of Rows In Report Depending On Number In Another Field

Nov 4, 2013

I have a form where we fill in information for supply of equipment to employees.

Each item must be signed for on a printed report.

I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.

For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.

View 11 Replies View Related

Delete Query To Target Fields Not Rows?

Sep 5, 2007

Hi

I need to know if it is possible to create a delete query which will target particular fields in a row based on certain criteria in a seperate combo box(on same table).

The few ways that I have tried to do this have been unsuccesful so if anyone can shed some light on the subject it would be greatly appreciated.

View 5 Replies View Related

Add, Edit, Delete Fields In Access Database Via ASP

Apr 13, 2007

I have recently gotten a handle on ASP and found it to be a very powerful tool.

I am wondering if there is a way to design a page where I can list my database tables, and by using a command, I can use asp to do the following.

1. Add or delete table
2. Add, delete, or update fields within the tables

for instance: rename a field 'PersonName' to 'MyName' without change the original values in the field 'PersonName'

I have searched all over the net and could not find information on this subject.

Thanks in advance.

View 3 Replies View Related

Forms :: Delete Search Field In Order To Point To Appropriate Field?

May 6, 2013

I am using access 2010. I technically have an unbound form but I am changing the record source by command buttons. Switching between 3 command buttons. My problem is that I have a subform that I was linking to the form to an unbound search field. Now I want to switch the master and child links to a field on the form when I switch the record source which happen to be the same field as the unbound search field; however; it still pulls from the unbound search field rather than the field on the form. Do I need to delete the search field in order to get is to point to the appropriate field?

View 1 Replies View Related

Find The Lowest Number And Place The Number And Column Name Into In Field

Dec 15, 2005

I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name.
Example:
Starting file
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC
05512,3,2,4,2

ENDING table needed
DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, NEWCOLUMNname
05512,3,2,4,2,2 ORIGIN2 ORIGIN4

Where the new column name contains the MIN number in the row and all of the associated duplicates column names.

View 1 Replies View Related

General :: Getting Invoice Number Field To Auto Generate Next Number

Jun 2, 2014

I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0

Code:

Private Sub Customer_AfterUpdate()
If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

invoice numbers are 04024, 04025 etc...how I keep the formatiing?

View 5 Replies View Related

Referring To A Combobox Value

Jan 4, 2005

hiya..how do i refer to a combobox value from a criteria in a query??
i.e. criteria for a field in query = combobox value in a certain form

View 2 Replies View Related

Cannot Delete Field !!!

Jul 12, 2006

I have removed the relationship and still can't remove a field. Any help??

I also tried compact and repair and then still will not delete.

View 1 Replies View Related

If Statments And Referring To Other Tables

Sep 25, 2007

I am new to access and am having troubles figuring out if statements. I want an if statement in a table that says if column 1 = "whatever" then column 2 = "YAY" else column 2 = "NO".

How would I apply this to a table?

Also I have a table that has one column it is a list of codes. In another table there are 2 columns column 1 has a code i want column 2 to say if column 1 code is found in the list of codes from the other column the put "MATCH FOUND"

Thanks,
Mike

View 12 Replies View Related







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