I am new to MS Access. I am using Office 2013.
I've created a database at my work place, in which I wanted to add my requisitions. What I wanted is to have an auto increment field for my reference number field, that field is like 001/Jan/15. In which first 3 digits refer to the requisition number, then month and then year.
How can I create an input mask with auto increment to solve out this problem.
I copied some VBA from one database to another. I didn't change anything and I am able to run it fine in the first database. But in the DB I pasted it to, I am received a Compile Error message with the XlApp As Excel.Application area highlighted.
Function OpenAutoCount()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
[code]...
I have a line of code in an old program:
Dim CN as ADODB Connection
This is giving me the error 'User defined type not defined'. I know I have to set something in a list somewhere but have forgotten how to do that. Where to go, and what to set?
I have a database which has a contact table and a flight table joined in a one to many relationship. Each contact may more than 1 flight and I want to be able to report on people that have exactly the same flight pattern. For example if 2 contacts have flight records FlightID 1, 4, & 10 I want them to be grouped together.
I have tried a number of things including a cross tab query to end up with the contactID and then a string of their flight IDs but as there are over 200 possible flights, the string appears as 1,,,,,,,,3,,,,,,,,,,,,,5,,,,,,,,,,,,6,,,,, etc and when I try to group on this it doesn't seem to work. This seems a very messy way of doing it and I'm sure there must be a much easier way!
Help!
Currently I'm working on an invoicing database in which I can register my customers (or partners) and also make invoices and purchase orders. So far, it does exactly what I want it to do, but I'm struggling with the invoice numbers. What I want to have is that whenever I make a new record, the next invoice number in sequence is automatically assigned to it. Another desire is that this number is prefixed with the current year, and the counter should reset with every new year. So, for example:
2015001
2015002
2015003
etc.
Then
2016001
2016002
etc.
Don't worry about the maximum of 999 invoices, because I won't ever go beyond that limit.
I am trying to get the list of all user defined tables from the Access database.
If I use the following query in Access it's working fine and getting the result. But if I am trying to execute the query in ASP page, it's not working. I am getting an error ([Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'.)
Can u please tell me the reason.
SELECT MSysObjects.Name AS TableName
FROM MSysObjects
WHERE (((MSysObjects.Type)=1)
AND ((MSysObjects.Flags)=0));
Note: If you have anything, to extract user defined tables, please tell me. Either one works out for me.
Hi all!
Got yet another question.
I have a text box field (txtLoanSN) for users to input serial numbers, but they can be of different formats depending on the type of service.
Examples are:
Type A - 020/2005, 001/2006
Type B - C2001/2006, C2006/2006
Type C - B1001/2005, B1021/2006
I would like to perform a series of actions based on the type of serial no. e.g. disabling another textbox (txtPNo) if txtLoanSN is like Type B or Type C.
How should the syntax for the If Statements be?
Would appreciate any help I can get!
Thanks!
My table key looks like this. Its a table that keeps a master record of conferences that occur during a calendar year
ConfYear (Date/Time) primary key
ConfNo (Byte) primary key
As you see its a composite key. Now each time a new record is created i want the ConfNo to autoincrement within its ConfYear. I used a byte as its not usually more than 4. Autonumber does not work as part of a composite key i found out.
2014 1
2014 2
2014 3 etc
And revert to 1 when ConfYear input changes to 2015
Is there a simple way to do this with say, a macro, or is the only way with VBA and DAO?
Hi,
On Compiling my assecc database VB code I get the following error message "User defined type not defined". I understand it is beecause I have not declared the Variable Type, but have no idea to exactly which part of the code the error is referring to.
How do I find out WHICH User defined type is not defined, especially when I have not got any (or do not want to use any) user defined types?
Thank you in advanced programming wizards. Kind regards, Adam.
Hello,
I have the following code and i don`t know what's wrong
Private Sub cmdCautare_Click()
Dim strSQL As String, strOrder As String, strWhere As String
'Dim dbNm As Database
'Dim qryDef As QueryDef
'Set dbNm = CurrentDb()
strSQL = "SELECT DOSARE.DosarID,DOSARE.DenumireDosar,DOSARE.CodDosa r,DOSARE.DataDosar,DOSARE.Denumire,DOSARE.Data,DOS ARE.Stadiu FROM DOSARE"
strWhere = "WHERE"
strOrder = "ORDER BY DOSARE.DosarID "
If Not IsNull(Me.txtDenumire) Then
strWhere = strWhere & "(DOSARE.DenumireDosar) Like '*" & Me.txtDenumire & "*' AND" ' "
End If
If Not IsNull(Me.cmbStadiu) Then
strWhere = strWhere & " (DOSARE.Stadiu) Like '*" & Me.cmbStadiu & "*'"
End If
DoCmd.Close acForm, "frmPrincipal"
DoCmd.OpenForm "frmRezultateCautare", acNormal
Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder
End Sub
Here: Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder
The error is the following "Application-defined or object-defined error"
Thanks!
I'm currently in the process of doing a an overhaul of the database including a table, some odd 17,500 records long.
What I need to do is to create a unique identifier from the existing part number, almost like an autonumber, but it only increments when there are duplicates. What I mean is that there would be for example, there would be five records of say, part 000135.
What I'm trying to get at is, how I would have to code to grab the existing part numbers (all formatted to be 6 digits), add an extra 2 digits to be its unique identifier, and then save it all to a Unique Identifier column, that I created.
An example of what I want is say, I have part numbers 000135 and 000136. The numbers would go up from 000135-01, 000135-02, 000135-03, 000136-01, 000136-02.... The dash is not as important as implementing the 2 digit unique identifier.
I have an access database which import the .csv file but after importing it is creating the tables
"google_ImportErrors"
If i won't delete this, for next time i would open the database it will create the tables and follow the same sequence like stated below and it increase the size of the database so deletion must be required which i do manually.
"google_ImportErrors1" (table names)
"google_ImportErrors2"
"google_ImportErrors3"
"google_ImportErrors4"
I want to delete/drop all tables with a query that have the names like "google_ImportError...", how to do that in automation?
How to make a custom ID format that increment every time you add a new record the sample ID look like this "HCCR-SMA-CV-ST-000".
View 4 Replies View RelatedI have two tables, Articles and Versions, related 1 to many. In my Versions table I want to set up a field that will start at 0 for the first version record related to an article record, and as version records are added in relation to that same article record I want it to increment by 1, no gaps. It ideally would be editable. It will not likely count above 5.
The result would be
Code:
article1 version0
article1 version1
article1 version2
article2 version0
article2 version1
article2 version2
article2 version3
What's the best way to accomplish this?
Hi all,I'm trying to attach some table from a Sql Server database, but when I take a look to the content, I see all the fields filled by the value "#CANCELLED".I supposed that the matter of such an issue could be the datatype used for some fields, a user-defined datatype. The only table content from the same Sql Server database I can see does not use that data type. I'm using a read-only account to access the Sql Server database. If I try to import the table, the data are imported correctly and the user-datatype is converted to text.Any suggestions for a solution / workaround ?Thanks Bye
View 4 Replies View RelatedDim XL As Excel.Application
When I try to run this specific line of code an error occurs. It says:
"User-defined type not defined"
May I know how to solve this problem?
Thanks a lot
I've put in this in a module many times and this is the first time I have gotten an error.
Dim Conn As ADODB.Connection
When I run the app I am getting a compile error saying "User-defined type not defined".
Anyone have any ideas?
Thanks,
B
hi,
I am new to this forum and to MS Acess. i am not a software engineer or in the field of software. I had to learn as much as i could about MS Acess because of a project i worked on. I have a standalone MSacess database and one of the forms is giving me trouble when I try to enter a new record
The form is called frm_fragrances and has information about a fragrance.
the frm_fragrances has 4 fields in it and a sub form. the sub form has details about the fragrance
In a new record when I enter the 4 fields and attemt to go to the subform which has details about this fragrance I face this error.
an unexpected error haas occurred @2465: application defined or object defined error.
additional information
Active form:<frm_fragrances>
Active control:<txt_VendorName>
Previous control:<frm_fragrances>@Please call the developer for further instructions
I have looked in the FAQ and on this forum for help on error 2465 and cannot find anything to help me. I can work with forms to an extent but cannot write too much code.
I would really appreciate some help in this matter
Thank you
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
View 8 Replies View RelatedBeginner with Access 2010 - making additions to one field in a JunctionTable. Need to extend a field by adding some new numbers but have found it easier to Export to Excel - make changes and replace in accdb.
I have found an Excel formula that does what I want but starts from the number "1".
=INT((ROW()-1)/9)+1
Which starts with number "1" and repeats this 8 times then increments by one and repeats 8 times.
But I want to start with a number of choice (7270 and go to 9028). Have seen some VBA versions but none give me control of the start number. I am then able to update the second empty field on the JunctionTable from a query. (this is a fixed number).
Hi All
How to assign value to a field based on the value of the same field in the previous record?
Autonumber has its limitations for my purpose. Because, after I start filling up a form, I some times cancel the form filling process, and it affects the continuous numbering.
I guess the issue can be resolved by using some thing like record count facility. i.e. current' record's field value = number of records till previous record + 1.
How to exactly codify or execute the above by vba coding.
The Field Name is "SerialNo.", and Table Name is "StudentData"
Thanks
Padhuka
I have a database that has a few tables and in one of them (Originators), there is all the info about my loan originators including there comission level. I would like it when they turn in a certain amount of loans there commision level will increase. Is this posible? Here is my DB setup.
Tables:
Originators- 1 to many-
OriginatorID,FirstName,LastName,OrigSSN, Address,City,State,PostalCode,HomePhone,WorkPhone, CellPhone,FaxNumber,EmailName,BirthDate,Level(This is the commision level, and its in %).
Customers- 1 to many-CustomerID,FirstName,LastName,SSN,HomePhone,MobiPh one,FaxNumber,Address,City,State,Zip,Email,BirthDa te,CreditScore,SpouseFirst,SpouseLast,SpouceSSN,Sp ouceBirthDate,SpouceCreditScore,OriginatorID.
Loans- many to 1-
LoanID,LoanType,StartDate,EndDate,LoanLender,Custo merID.
Lenders- 1 to 1-
ID,LoanLender.
When a customer is added to the DB it is done with a form that has OriginatorID(which is autocreated), CustomerID(also auto) and some other of the customer stuff like phone numbers. When this happens I would like to have the originators % level increase, say after every 4 customers entered. Is it possible?
Thanks,
Scott
I am importing an Excel file with existing data using the Import wizard to build my table. It has an ID field (CID) which I would like to keep and set to auto increment starting with the next number.
Can this be done ?
Hi,
Need a little help with rounding up. In A2K I have a form with a textbox that displays a security deposit. Security deposit is calculated by rounding the payment to the next $25 increment, hence a payment of 324.53 should have a security deposity of $325.00, but a payment of $325.01 should have a security deposit of $350.00. The code I am using for a datasource for txtSecurity deposit usually works correctly, but for this payment ($324.53) it rounds to $350.00. Here is the code..
=(([txtPayment]25)*25+[txtSecDepRndTo])
txtSecDepRndTo holds the $25 incremental value.
Incidentally, if the payment is $324.49 my code rounds the Security Deposit to $325.00 as it should.
Any help would be greatly appreciated.
This is the problem. If I have a field that contains something like this:
GHP-K-0000D
How can I make the number portion (0000) increment by one when a check box is checked?
Thanks in advance
i have 2 field "CLIENT ID" and "CLIENT CODE". when i post a new record, i want the "CLIENT ID" and "CLIENT CODE" to merge to give me 1 field "CLIENT". the CLIENT CODE should be prompted for eg. THO or BLT and added to the CLIENT ID which will compose of 001 or 002. The CLIENT ID should also increment when each new CLIENT record is posted.
The example should make things clear.
CLIENT ID CLIENT CODE CLIENT
001 THO THO001
002 THO THO002
001 BLT BLT001
it should look up the last CLIENT value and then for the new record just continue on the previous entry.