Getting The Autonumber Into The Form After Inserting New Record

May 14, 2005

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

View Replies


ADVERTISEMENT

Queries :: Check If Record Exists Before Inserting From Form?

Jul 2, 2014

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

View 10 Replies View Related

Refresh Main Form After Inserting New Record In Another Form

Jan 24, 2012

I 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?

View 1 Replies View Related

Modules & VBA :: Inserting Field Value When Adding Record Using Data Entry Form?

Dec 14, 2014

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.

View 7 Replies View Related

Forms :: Autonumber On Form - Automatic Generating New Record?

Oct 23, 2013

I am working on school project. I created priority table that has primary key (ID number) and other data, and three tables(also with data) that are linked to this table, with ID number of priority table and primary keys of other tables (relations many-to-many, I formed tables between them).

Problem: - my solution is created so that I can fill all neccessary data of ALL tables in ONE FORM. But ofcourse Access wants you to enter at least one field in primary table for opening new record, so that It links you to all other tables (in my case with ID number) - then It is possible to enter data of all other tables in whatever tab order you like.

What I want to do is that my autonumber field from primary table would automatically open a new record number when opening the form, without entering any data to primary table. Is that even possible ?

So far I tried to add another field in my primary table (Date/time format) and set the date to =Now(), and used macro of Refresh on form, but nothing happens. Date is shown in field, but Autonumber doesn't generate new number of record. maybe I did it wrong...

Any option to lock Tab order and force user to fullfill data only in desiring order ? I want to make solution more user friendly

I'm using Access 2003...

View 2 Replies View Related

Modules & VBA :: Linking Tables By Auto Inserting A Record Using Current Record

Aug 19, 2013

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]....

View 5 Replies View Related

Problems With Inserting New Record To Db

Nov 11, 2004

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.

View 2 Replies View Related

Inserting Text In Front Of Record

Oct 13, 2005

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

View 7 Replies View Related

Combo Box Not Inserting Record In Table

Mar 14, 2005

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

View 2 Replies View Related

Problem Inserting A Record After A Deletion

Apr 25, 2006

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?

View 5 Replies View Related

Queries :: Inserting A Record Number?

Apr 9, 2013

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?

View 5 Replies View Related

Inserting A Record With A Date/time Field

Feb 2, 2005

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?

View 1 Replies View Related

Inserting Or Moving A Record To A Different Position In The Table

Oct 14, 2005

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

View 6 Replies View Related

Deleting The Last Record Where Key Is Autonumber

Nov 3, 2004

I have a situation where I need to delete the last record I entered.

This is the sequence

1. The user selects that they want to add a new record (customer order)
2. I (using a macro) append a new record with some information filled out. This is shown in an input form (with lots of other info displayed) and I save the autonumber key to an invisible text field.
3. The user can then either SAVE or CANCEL. In the cancel I need to delete the record that was just added (see step 1.)

The problem is I need to close the form before I delete the record (thus removing the invisible text field containing the key to the newly created record). Once I close the form I don't know how to determine which record to delete (since I no longer know the Key) and I can't delete the record while the form is open since it is locked.

I am probably not doing this incorrectly but my issue is:

How do I delete (using SQL) the latest record an individual has created (which will always be the highest autonumber Key with the userID equal to this users's ID)?

Any ideas????

Thanks

View 1 Replies View Related

Autonumber Not Updating Until End Of Record Set

Feb 2, 2012

I constructed a database in access 2007, when you entered a new record the autonumber would be generated once you started typing in values. I have since upgraded it to an SQL back end and now the autonumber does not update until the recordset is completely entered. This is causing me serious problems with my subforms since the record will not update before I need to go to the subform.

View 1 Replies View Related

Insert New Record To An Autonumber Field?

Jan 31, 2005

So, I have a table with an autonumbered key field. I started the autonumbering at 1000 thinking that I may want to manually backfill some old records at a later date. Now I’m stuck.

Is there a way I can insert a new record into this table, filling this field as “950” for example?

View 3 Replies View Related

Inserting Objects Into A Form

Aug 26, 2005

This may be a fairly simple answer (and I hope it is, I really do) but I'm coming up empty searching through manuals and whatever google has on the issue, so I'll toss it out here in hopes that someone can help me.

Is there a way to add a "browse" button or something similar to an Access form field intended for files (word documents mostly)? I'm developing a process management database fror my office, and while I can master the whole "right-click-insert-object" thing, it's really more than the non-technical types in my office are going to be able to muster. Appreciate any help!

View 2 Replies View Related

Inserting Records VIA Form

Aug 31, 2005

Hey guys, got another question for you.

I have a table that has the follow fields (this is for a mailing schedule)

Job Name, Mail Date, Job Number, ID #, Material Due Date, Quantity To Mail

Here's the problem I'm having.

For each "Job" that we have, it can have multiple "Mail Date(s)". I'm trying to create a form that will allow a person to enter one "Job Name" and multiple "Mail Date(s)" and have it create a total number of records based on the number of "Mail Date(s)" (total possible is 4). Example below.

I'm creating a mailing for "Free Hot Coffee", I enter the "Job Name" once, and enter 4 "Mailing Date(s)", then click a button and have Access add 4 records for "Free Hot Coffee" each with a separate "Mail Date"

Is this possible? If so how? I was thinking of using a For loop but didn't know if it was possible.

Thanks in advance.

View 3 Replies View Related

Form Inserting Into Both Tables?

Nov 15, 2006

Hi,
I have a problem with a form based on a query. It draws content out of the table Repairs, and also shows the customer info for each repair (based on the link between CustomerID field in both the Repairs table and the Customers table)
But when I add a new record using the form, a new entry is added to both tables.
Can anyone please explain why this happens and how i can fix it?
I dont want a new entry in the Customers table, only in the Repairs table.
Hope this question makes sense.
Vauneen

View 1 Replies View Related

AutoNumber Generated In Case Of Record Not Entered.

Dec 13, 2005

I m using autonumber in my ID field.My other table fields are mandatory.
There is no problem when I enetered Record completely.
Now if I have 20 records. automuber is also 20. Now I want to eneter new record. 21 autonumber generated. But I have not completed the form/table.or I didnt want now that record.So I didnt save it.

But next time I come autonumber 22 is generated for my new record.
Now I have 1,2,3,......19,20,22,23....

here 21 has lost.Now is there any option to recover or restict that without completing the full table if the record had not saved autonumber again generated.

View 5 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

Tables :: Set Autonumber To Start From Last Record On The Table

Jun 10, 2015

I had a backup from a table that I saved to excel. Somehow we lost all the records from the table 10 minutes later. There where gaps in the id numbers due to some delete records in the past, and when I did try to put it back in the access table the records some how shift. is there a way of still using the ID nr that is an autonumber in the table and when I set the data back from excel and use the append when paste that they show up correctly? Maybe I can set the autonumber to start from the last record on the table?

View 14 Replies View Related

Modules & VBA :: How To Get Autonumber ID Before Update / Save Record

Mar 27, 2015

I wanted to get the autonumber ID before a record was saved to the table. My fields are on a form that is linked to the table. Maybe my solution is not the most elegant but it seems to work.

I messed around and came up with this solution: it creates the next record and captures the autoID then increments it and creates the record we will actually use. Since we know the current autoID we know 100% the next will be the current+1

Code:

' Code by Witchcraftz
' Button event to add new record
Private Sub cmdAddRecord_Click()
Dim strID As String

[code]...

View 2 Replies View Related

Modules & VBA :: Reset Autonumber After Record Deletion

Jul 16, 2015

We need to reset the autonumber after the last record is deleted.

For example:
2006
2007
2008

if 2008 gets deleted, we want the autonumber to reset back to 2008 rather than move to 2009.

Is this possible? This is the code we are using but cannot get the string to recognize the variable. If the variable is replaced with a number, it works, but it defeats the purpose of adding the plus 1 to the last autonumber.

Code:
Private Sub Command0_Click()
Dim RLMax As Integer
Dim Statement As String
RLMax = DMax("[id]", "Table1")
RLMax = RLMax + 1

strSQL = "Alter table table1 Alter Column Id Autoincrement(RLMax,1)"
DoCmd.RunSQL strSQL
End Sub

I realize we don't want to depend on the autonumber for anything other than a row identifier, but the table is setup that it is important for the rows to be sequential if the last record is deleted. Only if the last record is deleted.

View 12 Replies View Related

Modules & VBA :: Duplicating Record With Autonumber Field

May 23, 2015

I am wanting to create a new record in a table copying the majority of the data from another record in the table. I am using the following code which creates new records but the data isn't being copied.

This is the section of code:-

' Return Control object variable pointing to list box.
Set ctlList = Me!lbStudents
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Get the info to get the enrolment record
intClientID = Me.lbStudents.Column(7, varItem)

[Code]...

what I'm doing wrong?

View 2 Replies View Related

Inserting Data From Form Into Table

Nov 2, 2005

i have a problem adding the data from the fiels on the form into a table. I know you can just click on the navigation arrows and it will save the record but i want to use a submit button as this is more user-friendly and suitable for the work i'm doing.

I have attached the database... the form that needs the code for submit button is 'frmNew_JobStatus' and the table i'm trying to insert the data into is 'tblJobStatus'

can anybody have a look at what i've done so far and suggest how i can fix this... sample code would be very usefull as i'm not an access expert.

thank you all

View 1 Replies View Related







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