I am using the following to add a column to an existing table. How do I make this column indexed with no duplicates?
db.Execute "ALTER TABLE [BrandTBL] ADD COLUMN UPCGroupName TEXT;"
I have some code that creates a table based off another table. This code works perfect but Im trying to alter my ID field and change it to an auto increment. How can I fix my code so that it alters my ID field?
Code:
Private Sub Command0_Click()
'OBJECTIVE: to build a table by extracting some fields from a main database
Dim rst As Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim intCount As Integer
[Code] ....
I have downloaded a contact template from the internet ready to go. There are few things I need to get rid off and make changes to.
One of the most improtant thing is making one of the field unique. For some reason, i can get to the design view. I open navigation panel and right click on the table, there is no such thing call design view on the menu.
I have a calculated field which consists of a few other fields concatenated, i.e. [field1] = [field2] & [field3] & [field4].
I need field 1 to be unique when its concatenated, but fields2-4 don't need to be unique. Is there a way to do this?
I am attempting to update an oracle table using MS Access.
One of the fields to be updated is a primary key number field.
I'd like to use the autoincrement field to update that field.
My sql is:
Code:
Alter table testAutoNum Add column progid autoincrement (1001,2)
This sql nicely creates the new field in the table, but it doesn't start at 1001 and increment by 2; it starts and 1 and increments by 1.It is also creating an autonumber and I need it to be a number.
What am I doing wrong?
I have a table called login and inside that table is three columns: username, password and admin.
I have the username saved in a global variable called GsUser. How can i find the record in that table with the same Username as the string stored in GsUser and use that record for an if statement which sees if the value of the admin column is "Yes". Im trying to do it using VBA. Im not using a form where everything is bounded.
I have a form with two list boxes on it: listAllBANANA and listActiveBANANA
The listboxes are populated from tblBANANA. Active is defined by the tblBANANA field "ResponseDate" being Null.
Also on the form is a CloseBANANA command button. When pressed with no selection, it opens frmCloseBANANA to the first record. If a BANANA is selected in listActiveBANANA, the form opens to the selected record.
Now comes the tricky part: if a BANANA is selected in listAllBANANA, I first need to verify they have an Active BANANA selected, before opening the form. I've poked and tested and searched and cut and pasted my little fingers off with no joy. What I've deduced is that I'm either messing up the syntax of the DLookup or I'm incorrectly referencing the selected record. There's also the third option that I'm going about this in entirely the wrong way.
All other code below works. If I delete or comment out the line in red, I have no issues other than I may open up a blank form because I'm trying to close a BANANA that is already closed.
Code:
'If a BANANA is selected in Active BANANA or All BANANA, open Close BANANA to that record. If not, open form to most recent record
Private Sub cmdCloseBANANA_Click()
If Not IsNull(Forms!frmGUI!listActiveBANANA) Then
[Code] .....
how I can make an Array and For Each statement work together. I normally don't use either, but it would be good so I could write less code where plausible. Here is the example I have:
Code:
Dim Named As Variant
Dim Ctl As Control
Named = Array(Me.Namee, Me.ID, Me.Title)
[code]....
I am unsure how I include the array in this statement. I have tried replacing "Me.Controls" with the array name, but I get a 424 runtime error (no object). What I am trying to accomplish is for each item in the array I want to make it not visible if it has a null value.
I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3
INTO [tblTemp]
FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3,
False AS Field4,
False AS Field5
INTO [tblTemp]
FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:
Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))
I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.
have a query which I would like to export to excel 2010 and would like separate files saved using a unique field called [Brokerage]. The code below exports the query however does not export separate worksheets as I am missing something perhaps the OutputTo function.
Private Sub Commission_Excel_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim temp As String
Dim mypath As String
[code]....
Have a continuous form with 2 date fields in each record .
If dateField1 is empty ,Then I want a way to make DateField2 Take its Place .
I know how to make things happen with conditional formatting on one field by UsingIsNull ,I have tried sending datefield2 to back.But cant work out how to bring it to front If DateField1 IsNull.
I have an access front-end package that works just fine but if there is an update, I just want the program to run scripts to update the SQL Server tables that it is connected to. I just don't know how to do this. I know how to do this on SQL Server Management studio and all, I just was hoping I could do it straight from my access front end so that a user could just click a button and update the tables or something.
View 9 Replies View RelatedI have a form called Orders.I need to lock this form once an invoice has been printed .
I have a check box on this form ,that if ticked should lock that specif order .
This is my Code:
Private Sub Form_Open(Cancel As Integer)
If Me.lockorder = True Then
Me.AllowEdits = False
End If
End Sub
The problem is it does nothing . I am using the open event of the form .The checkbox is called LockOrder.
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
I have two tables (Access 2010). One with a list of names (List1) and another with a very similar list of names (List2), but they differ in very small ways. For example, List1 might have John Smith, and List2 would have Smith, John L.; and Smith, John. List2 also has a unique ID associated with these names that I need to append to List1.
I need to design a form that will allow me to look up names in List1, and have it return all names that are similar in List2. I then need to be able to choose with record in List2 matches with the List1 entry (based on a few other columns in List2, such as birth date) and have the form add that unique ID to the List1 record.
PS: I am using Access 2010
hi,
i have a raw data table built, may i know how to hide the table to not allow users to alter the raw data table?
I use the Tool > Startup option to hide everything but if the users are clever enough, they would able to make that available and try to alter the raw data table which i want to prevent and hope that's not gonna happen.
Hi,
I wonder if someone can help with what must be an easy solution to this problem.
I have a table with 350,000 records, but without a unique identifier field. I just want a simple incrementing number field to become the primary key but dont know how to add this in. I cannot add an autonumber as there are two many records - it falls over.
Ive tried exporting it all to excel, but as well as being cumbersome, something went wrong and so I'm back to square one.
I think there must be a way to add an incrementing number either via a query or in VBA but I dont know how. Please can somebody provide a solution.
Thanks,
Adam.
Have a table with data in it and I want to add a new field to uniquely identify each record (simple numerical sequence like 1, 2, 3, 4.....). I don't know VBA and don't know SQL.
Tried opening the table in design view, adding a new field with "autonumber" as the data type but get the following error msg: "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry". The table has about 750,000 records in it.
I am trying a to build a slot booking database in which users will be able to book slots (ranging from 1-30) on a particular day for a specific site (location).
When trying to build the relationship between slot in tbl_available and slot in tbl_appointment i get the following error "No unique index found for the referenced field of the primary table" the same error pops up when trying to build a relationship between site in tbl_available and site in tbl_appointment.
I need both relationships to be 1 to many.
I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". For example, if costumer "Jack" has ordered as the following:
Code:
ID costumer product quantity unitprice orderdate
12 jack A 4 10 25/11/2014
13 jack B 5 5 25/11/2014
14 jack C 1 4 25/11/2014
15 jack F 3 2 25/11/2014
16 Jack G 4 8 25/11/2014
17 Jack H 1 7 25/11/2014
Then I make a report based on the above table and issue that as the invoice to the costumer, who is jack here.
How can I make a unique invoice number, which automatically increases sequentially for each invoice? Do I need to add a new column to the table? I also need to be able to look for a specific invoice based on the number, or date.
I am trying to write a statement and it returns: #Type!. I know what this means but it should not give this error.I have button that copies a certain number of fields to the clipboard. The one below is one of them. Here is the coding I am using that works:
Code:
=Trim(IIf([CallingContactRel] Is Not Null,[CallingContactRel] & ": " & [CallingContact],[CallingContact]))
I would like to add the following condition to this code. I have a yes/no field called BookingNoteContactExcl. If this is ticked, it should exclude [CallingContact] from being copied, in other words, return "" in the code above.
I want to add a column to an existing table using the sql statement Alter table. The new column needs to be a yes/no type. The following code almost works but it only sets the column to a general logical type not specifically the yes/no check box .
DoCmd.RunSQL "Alter Table TblIssues Add [" & Issue & "] logical;"
Any ideas?
"ALTER TABLE MOVIE ADD ( MULTIPLEX NUMBER(3))"
while i'm trying to execute this query ...
Its displayed as 'SYNTAX ERROR IN FIELD DEFINITION'
Pls help me.... :confused:
Hopefully someone can help....
I'm trying to change the name of a column in a table using SQL. I've tried different combinations but always get
"Syntax error in Alter Table statement"
SQL I've tried
alter table tblImport
rename column F1 to [Date];*
alter tblImport
rename column F1 to [Date];
alter table tblImport
rename F1 to [Date];
I've checked this out via some SQL sites & think the first version (*) is correct....can someone throw light on this? I'm using Access2000
I need to alter filed 24-gen-64, in 24/01/64... how to?
View 5 Replies View Related