I have a query with records,
I want a field named RECORD NUMBER beside it I want a consecutive number increment EG
RecordNumber - RecordDecription - RecordTitle
------------------------------------------
1 - TITLE001 - TITLE001
2 - TITLE002 - TITLE002
3 - TITLE003 - TITLE003
is there a formula i can use in my query to create this?
The problem I have is, that I need to insert an apointment into de database but first I need to verify if there is a record or an apointment in that room that day the same hour, if that is so send a msgbox saying an apointment already exist in that room this day at this hour.
i tried using dlookup but it only works in one record using just one criteria
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
I am just beginning to manipulate Access from Excel.
I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.
I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.
When I try to use the second version I receive the error message...
Number of Query Values and Destination Fields are not the same.
The string that works perfectly is:-
Code:
strSQL = "INSERT INTO Clubs (ClubNumber,ClubName,ClubGrade,ClubRegion,ClubPosition,ClubHasHistory,clubinleague,cluboriginalposition) VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The one that generates the error message, which is identical except that I have removed the list of fields is:-
Code:
strSQL = "INSERT INTO Clubs VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"
The execute command is:-
Code:
gcnConnection.Execute strSQL, , adCmdText + adExecuteNoRecords
Just fumbling my way around Access 2007, is the only way you can create a drop down list of number by creating a table and inserting a combo box? I am trying to create a list of hours for example:
0.5
1
1.5
2
Etc...
I want to create an update query to update 20 record with the first item in combolist then the next 20 record updated with the second item in list...
View 2 Replies View RelatedMy table appears like this with the following columns:
Name| CUSTNo| ORderNo.| AcctDate| OrderDate| OrderDescription
I would like to write a ms-sql query to return the records of each customer number with the latest OrderDate. One more thing, if the customer has two separate order numbers on the same date(should be the latest date), the query should be able to pull up both the records.
I tried with the code below but its taking a long time to execute and finally hanging up MS ACCESS.
SELECT * FROM TableName AS a WHERE Not Exists (SELECT *
FROM TableName b WHERE b.CUSTNo = a.CUSTNo AND b.OrderDate >= a.OrderDate);
Hi,
I am working with a simple MS Access database. I am trying to add a new user with the following SQL statement:
Set connection = Server.CreateObject("ADODB.Connection")
connection.Open(strCon)
strSQL = "INSERT INTO UserTable (UserID, FirstName, LastName, Password) values ('" & userName & "', '" & userFName & "', '" & userLName & "', '" & userPWD & "')"
connection.execute(strSQL)
However when trying to run the code, IE gives me the following error:
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/dvdcollection/registration.asp, line 60
Line 60 is the connection.execute(strSQL) line.
I have double check the statement but cannot see anything wrong with it.
I would appreciate it if someone can help me out on this one.
Thanks.
I have a field and in front of all records in this feild I would like to add a fixed text. e.g. if the record is XYZ i want to add abc in front to make it abcXYZ. Sorry newbie here.
ThaNKS
hi
i have attached a document with print screen of the form and combo box properties i am working on
the way it works is, when the user selects a code, the appropriate desciption, uoi and price is displayed. but for some reason the price combo value is not being written to the price field in the table. all the data on the form except for item desc and uoi is written to tbl_waste. the item info for the combo boxes comes from qry_items.
please help..i have been banging my head for days now...thanks
I really hope I can explain this right :o
When a customer makes a purchase I INSERT the transaction into the 'transactions' table, which I have no problems with. My problem stems from that I can't seem to get the 'OrderNumber' back from the 'transaction' table (OrderNumber is an AutoNumber by the way).
I have tried the following code -
TempOrder = DLookup("OrderNumber", "transactions", "customerID = '" & Forms![frmOrder]!TempID & "'")
"Forms![frmOrder]!TempID" is the value I use from another form (to see who the customer is). I want "TempOrder" to be the OrderNumber that is created in the transactions table.
Regards, James
I have a form and a subform. There are a number of buttons on the form, allowing me to add, update, delete, etc. records from the subform. The process is that I select a record on the subform, its details are displayed on the form, then clicking on <Delete>, for example, removes it.
Each of these processes work fine on their own and also if carried out one after the other, for the most part. The one problem I'm finding is when I delete a record.
Firstly, the subform appears to refresh and the record is no longer shown. However, if I then try to select another row from the subform, I get a message saying 'record is deleted'. I click on <OK> to remove this message and I can then select the same row or another row without any problems.
Secondly, if I now try to add a new record (the code behind the button is just 'DoCmd.GoToRecord , , acNewRec'), I get a message saying that I can't go to the selected record.
If I close and reopen the form between each activity, all works fine, suggesting that something isn't refreshing properly. I've tried putting 'Me.refresh' and 'Me.requery' immediately after running the deletion, but to no effect.
Any suggestions?
Hi,
I'm trying to insert a record into an Access database from some ASP Code.
I have:
...
strSQL = "INSERT INTO tblUser (UserID, Date, Comments) VALUES " & _
(" & nID & ", #" & Now() & "#, " & "''" & strComments & "');"
conn.Execute(strSQL)
...
I'd have thought this should work but it doesn't.I just get a "Syntax error in INSERT INTO statement".
My date field, called "Date" (just in case that's a problem!) in my database has no input mask defined, it's just a basic date/time field. I've tried replacing the '#' symbols with single quotes, and doesn't work either. I haven't had any luck finding a definitive example of how to do this, even though it's trivial surely. All the similar examples I've found talk about how to update a record set with a date, or how to set the system locale, etc.
Any ideas as to what I might be doing wrong?
Hi,
With a table in open view can a record be moved to a different position, i'm using access 2003. Also is there any way to sort besides ascending or descending?
I've got a lot of tables to make and if I forget a single column/field and have to add it later (like I have done all ready numerious times) I'm almost having to recreate the table again in order to have the field fit in the correct spot in the table. These tables are bound to combo boxes so its important every thing is grouped for easy choosing.
Thanks--All help will be very appreciated
I have a load of data in an excel spread sheet that I'm trying to put into an access using Excel VBA (Jet/ACE SQL I think!?). I do this by constructing INSERT INTO strings and looping through my spread sheet. Let's say the primary key is associated with the field [Company] which holds the company names. A simplified version of what I was using (it all works great ) is below:
Code:
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = GetConnectionString()
cn.Open
cn.Execute strSQL
where strSQL =
Code:
INSERT INTO tblTestMDB ([Company], [Date])
VALUES ('BP', '30/09/2012')
Unfortunately, I am having issues with users adding the same company twice. I'd like it to:
- update the existing company info if the company already exists
- add the company if it is not already there.
I'm trying to do it in two stages. First an update, then an add. The first part works great :
Code:
UPDATE tblTestMDB
SET [Date]='21/03/2015'
WHERE [Company]='BP';
The second part: (I have tried numerous variations on the syntax):
Code:
INSERT INTO tblTestMDB ([Company], [Date])
VALUES ('BP', '30/09/2012')
WHERE NOT EXISTS (SELECT * FROM tblTestMDB WHERE [Company] = 'BP');
I find the error messages rather cryptic but they include things like "Query input must contain at least one table or query." or "Missing semicolon ( ; ) at end of SQL statement." (<== No I'm not!?
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ********
Public Function GetTargetType() As Variant
GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]")
End Function
************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ********
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!target_group = GetTargetType()
'Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
'[tblNmsConsumption.target_group] = GetTargetType()
End Sub
************************************************** ********
making sure I can insert this value once retrieved.
How to fix some records in my access table. It is a huge table more than 12k records!
In one of the field there are some data missing. The logic to reconstruct them is easy but I am not sure how to apply it in Access.
I have three columns one is the student ID, Year, term1 and term2
ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003
3311 2004 005 005
In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311
ID Year Term1 Term2
1234 2001 001 002
1234 2002 002 002
1234 2003 002 003
1234 2004 003 004
1234 2005 004 004
3311 2001 003 003
3311 2002 003 004
3311 2003 004 005
3311 2004 005 005
I have a form with many fields and one field has an OnChange event to run an insert query or log an entry in a table about the change. I get insert entry written with everything except one field.The OnChange event code is:
Code:
If AddressStatusFld = 2 Then
'If it has changed from Current to Non-Current create a Contact Log entry for this matte
If (Val([MatterShortNoFld]) Mod 2 = 1) Then
Me.OperatorFld = 16
[code]....
I just can't work out why the record in MatterContactsMade table has a null value for the field Operator.
inserting values from a listbox in to a table. I have a table with a field named: PrefReports. This field will hold data from values in a list box that the user selects.
Table: Profiles
Field Name: PrefReports Type: Memo
The user utilizes a form to input the values necessary for the query. I then have the query tied to a command button. When the command button is clicked, the query is executed.
The query is as follows:
INSERT INTO TblProfiles ( [Employee ID], FName, LName, Barcode, PrefReports )
VALUES ([forms]![FrmCreateNewProfile]![txtEmployeeID], [forms]![FrmCreateNewProfile]![txtEmployeeFName], [forms]![FrmCreateNewProfile]![txtEmployeeLName], [forms]![FrmCreateNewProfile]![txtBarcode], [forms]![FrmCreateNewProfile]!lstReports.Value);
How can I take what the user selects in the list box, and insert into my TblProfiles.PrefReports field? When I run the query, all other information makes it to the table except the list box values.
I have add my two tables
Main table calls Attack
Sub table calls Research
the main table attack is linked with sub table Research by Attack.ID and Research.attack_id from form automatically
The sub table Research have more than one record which is linked into the main table Example:
How can I get the data which is marked in blue into record 24 and the data marked in Red into row 23
using Unite_Equal column?
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
I have a form, has some fields, one of them is the current date, so when the user click (save )button , which make (add new record )to the only table I have the problem that .all fields are inserted in the table , except the current date !! it is a text box ( Now() )!!
View 1 Replies View RelatedI'm trying to insert 10% of a dataset from dbo_billing into another table Random_Temp. Another form is open when this query is to be ran that passess in the billyear and billmonth... I'm sure it's a syntax issue as I can isolate the random number part and it displays the appropriate data, I just can't re-write it to insert into the other table:
INSERT INTO Random_Temp ( indx, peopleId, audited )
SELECT TOP 10 PERCENT b.indx, b.peopleId, b.audited
FROM dbo_Billing AS b
WHERE (((b.billYear)=[Forms]![billing]![billyear]) AND ((b.billMonth)=[Forms]![billing]![billmonth]) AND ((b.recertifying)=-1))
ORDER BY Rnd(-(1000*b.indx)*Time());
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 RelatedI have a main form where information for a particular part number or job is entered. Should the part number not yet exist, Access will let you know. I have a button within the main form that opens up the Form_EnterPartNumber form which allows you to enter the new part number.
What I would hope to be able to do would be to refresh (requery?) the main form after creating a new part number and closing the Form_EnterPartNumber form..........but, keep the main form on the current record instead of resetting back to record number 1.
Is this possible?
create a macro that automatically fills in the next invoice number in sequence",,I'm assuming this macro would look at the value of the previous record and add a one to itI don't want to use a AutoNumber field as I might need
(a) to modify value or
(b) need to skip numbers.