Adding A Record To A Table From A Field List?

Mar 1, 2007

Hi,

I have a table that has records added to it using the following VBa code:


Const MyTable As String = "tblSampleSubmission"
Const MyField As String = "SampleName"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Dim LastDub As Double
Dim addString As String
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
Randomize
'LastDub = Me.txtStartValue - Was only used to start the random function later in series
addString = ""
For intCounter = Me.TxtStartValue To Me.txtEndValue
rs.AddNew
rs.Fields(MyField) = Me.SamPre & intCounter & Me.SamSuf & addString
rs.Fields("SubmissionNumber") = Me.SubNum
rs.Fields("CustomerID") = Me.CustomerID
rs.Fields("SamplePrep") = Me.SamplePrep
rs.Fields("Fusion") = Me.Fusion
rs.Fields("XRF") = Me.XRF
rs.Fields("LOI") = Me.LOI
rs.Fields("Sizing") = Me.Sizing
rs.Fields("Moisture") = Me.Moisture
rs.Update
addString = ""
If Rnd < 0.02 Then
'LastDub = intCounter
intCounter = intCounter - 1
addString = " DUP"
End If
Next intCounter
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

DoCmd.SetWarnings False
Dim stDocName As String

stDocName = "mroLOIAppend"
DoCmd.RunMacro stDocName

Exit_EnterBlast_Click:
Exit Sub

Err_EnterBlast_Click:
MsgBox Err.Description
Resume Exit_EnterBlast_Click




End Sub


What I would hope to be able to do is add a "standard" randomly to each SubmssionNumber (each SubmissionNumber might be 1-100 records). The record I need to add should be chosen at random from a list of 6 or so options and added at the end or middle or start of the job (SubmissionNumber) is this something that is easy to do or should I just give up and add it manually?

Thanks to everyone who has helped me in the past, it is getting me up to speed quickly. Access seems to be quite popular as I have contacted 3 developers to help with my dB but they are all to busy to help me so I am going it alone.

View Replies


ADVERTISEMENT

Adding A Record And Specified Field In Table?

May 20, 2013

I am using a form to add a record to a table and need to be able to specify one of the fields in the table that will be added to. This field is predetermined by another form selection. If there is a way to force a value on a title box with a control source this would also do the trick.

View 4 Replies View Related

Tables :: Adding A New Field When A New Record Is Added In Another Table

May 12, 2015

I have a table A in which I write down orders for cars. A record is an order. A single order may contain multiple cars in varying quantities.Each car has its components. Some cars may have some of their components common. There is a table B which indicates each car and its components required with their quantities required to build the car. There is a record for each different car.

Now suppose there is a new car we are going to produce so we need a new record in table B for the car and all its components. Also we need a new field in table A because people can now order the new car(in some quantity).

With form for table B we can introduce a new record. But how can we add a field in table A automatically after a record is added in table B?

View 9 Replies View Related

Adding List Box To Display Multiple Hyperlinks Per Record

Jul 24, 2014

I have a table which contains a number of records, each with a load of different columns. However, the new way of doing H&S splits Risk Assessments into Short and Long COSHH forms. Each experiment only has one Short form, but can have from 0-20 (or more Long). I need a way of adding a box to the form that will return all the associated Long forms for that particular record. I have tried using List Boxes and Combo boxes as well as creating a separate table for the Long Forms, but it isn't doing what I had envisioned.

List boxes appear to do what I want, however they just dump all of the results from the selected columns into the table. I only want the data for the specific record in question. For example, my first record has 6 Long forms, but a list box basically just pusts a snapshot of the table in there rather than the specific results.

All the Short and Long Forms have to be hyperlinked to the associated documents. I have successfully done this for the Short.

View 13 Replies View Related

List Table, Limiting Field List..

Oct 14, 2006

Okay,

The subject is probably confusing but I'll try to explain.
Setup:

Table1
FieldID = Number
FieldName = Text

Table2
FieldID = Number
FieldName = Text
T1_ID = Number

Relationship
Table1.FieldID 1-> * Table2.T1_ID (one to many)

Now, I'm trying to create a 3rd table that has field populated by Table1 and a field populated by Table2 but I want to limit the field populated by table2 by what is selected in the field populated by table1.
Table1:

1st
2nd
3rd
4th

Table2:

1st,January
1st,February
1st,March
2nd,April
2nd,May
2nd,June
3rd,July
3rd,August
3rd,September
4th,October
4th,November
4th,December


Table3:
Table1Field = Number, ComboBox-Source: Table1, BoundColumn=1
Table2Field = Number, ComboBox-Source: Table2, BoundColumn=1

If Table1Field = 1st then
Table2Field Will only allow January, February, or March as selections, since ONLY they are a member of the "1sr" group via the table1 list.

Is this possible, or do i have to do some run-time checking or something?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

View 1 Replies View Related

Updating A Field After Changing Or Adding Record

Jan 6, 2005

I have a text field "Record Last Updated" on a form formatted for date/time that I would like to update after a record is changed or added. So for every change or addition the field would update to the current date. The code I am currently trying to use is as follows:

'Assign current system date to Last Updated field if change of data occurs in any field
For Each ctl In Me.Form.Controls

If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) _
Or (ctl.ControlType = acListBox) Then
If Nz(ctl, "") = ctl.OldValue Then

Else
txtLastUpdated.Value = Date
End If
End If
Next ctl

This executes in the forms After_Update event procedure.
Problem is I get an error 3020 "Update or CancelUpdate without AddNew or Edit" when moving to the next record ? I have tried using .Edit and .Update but those come up as an invalid reference?
Any suggestions would be appreciated. Thanks in advance

View 4 Replies View Related

Adding One Field Of New Record Automically According To The Condition

Feb 21, 2008

hi all,

I am doing one project using access. I 've made two forms. In one form, lets say, it contains two common buttons. Both buttons will load the same second form and will add new record to the same table. When I click first button, it will load second form with all fields blank and one field of record will autofill one value,lets say "a" to the table.That field shouldn't be appeared in the second form,just want to fill automatically. Then the other fields of new records will be filled by the user input from the second form.
Also, when I click second button, it will do similarly but only the autofill value will be different from the first one.
Does anyone know how to make it?
If don't understand what I am saying, I can explain it again.
Any help will be grateful for me.

View 5 Replies View Related

Forms :: Adding New Record - Field Cannot Be Updated

Aug 31, 2013

I have a form based on a mysql table. There is a button in the footer to add a new record.

The pertinent vba code: DoCmd.GoToRecord , , acNewRec

It adds the new record and properly places the cursor in the first field. Immediately after the first letter is typed, the error message "Field cannot be updated" pops up. I can click ok and the message goes away and I am able to continue filling in the fields. The same thing happens if I add the record by use of the record selectors.

View 14 Replies View Related

Forms :: Automatic Population Of Field In Adding A New Record

Aug 1, 2013

how to automatically populate a certain field. To add some context, I have a form which registers the details of a contact with standard information of contact details. There is a subform which shows the different products that the client from the main form is interested in. This is a actually a data sheet which returns the results of a query (selecting from the relevant table the client in question and the products he/she wants).

I have added a button which opens up another form and allows a product (and hence a new record) to be added for that particular client. I would like that the form automatically populates one of the fields in the form that is the client id. Given that the subform is opened from a form which already identifies the client, how do I do this?

View 14 Replies View Related

Adding New Record With Table That Has Duplicate Records

Jan 26, 2005

My situation:

I have a table with duplicate ids. When I have a forum popup another form, the forms are linked by ids. This works great except for one problem. There are duplicate ids. So if I pop up a form then it will go to the first record that that id exist. This is not good if you are going to add another record. Therefore, I need to create a unique variable for a record. The autonumber would be a great variable but it seems I cannot use it to filter. Unless someone can show me how. Is there another way to create a unique variable automatically for a new record?

View 5 Replies View Related

Adding Record In Table Not Correctly Opened

Jun 25, 2006

The system is about a shop which gives credit facilities to its customers and so has to keep track of payments effected by them.

I have 3 tables: Customer, Payment, Balance. The main table is Customer, with primary key CustCode which is an autonumber. It keeps personal details of the customers. The Payment table keeps details of payments effected by the customers and the Balance table keeps the overall balance for each customer. The relationships are as follows:

Customer to Payment, 1-to-many
Customer to Balance, 1-to-1

Fields in Payment table: CustCode, PaymentDate, AmtPaid
Fields in Balance table: CustCode, Balance

Problem 1
I have a data entry form for Customer. When I create a new customer record, I want a new record automatically created as well for the customer in the Balance table. How can I do that?

Problem 2
I need to produce a report on customers who have not made any payments during the previous month. I’d like to know how to do that as well.

I hope I’ve made myself clear enough. Thank you for all help.

View 5 Replies View Related

Forms :: Adding Record To A Table From A Form

Nov 6, 2013

I have limited experience in Access, but I have managed to create a user form, where items are selected from comboboxes (Test results; when, who, outcome, etc)

At the end I have built in a command button that should add a record to an existing table, but so far no luck. I have used the following VB code for this:

Private Sub Save_test_results_KeyPress(KeyAscii As Integer)
Dim dbsICT_Test_Management As DAO.Database
Dim rstActual_test_results As DAO.Recordset
Set dbsICT_Test_Management = CurrentDb
Set rstActual_test_results = dbsICT_Test_Management.OpenRecordset("Actual_test_ results")

rstActual_test_results.AddNew
rstActual_test_results.Update
End Sub

View 6 Replies View Related

General :: Adding New Record To A Table By Using A Mask

Aug 23, 2014

I need to add only one record into a database by using a mask.

For sure when I call this mask via a button, this new record has to be added after the last one.

Someone is suggesting me to use function docmd.openform, but I really dont' know how to do it.

View 3 Replies View Related

Forms :: Adding New Record To A Table With A Form

May 22, 2013

Whenever I add a new record to a table with a form I created, it records the "ID autonumber" that it's associated with instead of the text. For instance, if I selected "Kevin" from a combobox list and had the form record it, it will show up as "1" in the table under the field name "Names".

View 3 Replies View Related

General :: Adding New Preset Record Into Second Table

Feb 26, 2013

I was trying to further my knowledge of access. I have a database that is suppose to be used a s a simple budget database.

Form: StartingAmount
Table: StartinAmount
Field: StartingAmount
Button: Create

Form: Transactions
Table: Transactions
Field:
TransactionDate
TransactionName
TransactionAmount

When the database is open the form StartingAmount is open (only until a starting balance is entered.)

User then puts the starting amount in. clicks create.

Then a record is added to Transactions.
TransactionDate = Date entered (auto populate)
TransactionName = Starting Balance
TransactionAmount = StartingAmount

I tried this code:

DoCmd.RunSQL "INSERT INTO Transactions (TransactionAmount) " _
& "VALUES(" _
& "'" & Me!StartingAmount& "') "

With this it kept giving me an Append error and Im not sure how to get it to stop and how to add all the fields needed.

I tired to do:

Code:
INSERT INTO Transactions([TransactionName], [TransactionAmount])
VALUES ("Starting Amount", Me.StartingAmount)

But i keep getting an expected error in the VBA side.

View 3 Replies View Related

General :: Adding A New Record To Linked Table

Aug 5, 2014

I have several tables that are linked that I need to be able to add to. This is an example of the structure:

Patient Info
ID
FK_Ward - ID of Ward table
FK_Room - ID of Room table
FK_Cond - ID of Condition table

Ward
ID
WardID

Room
ID
RoomNumber

Condition
ID
Description

Unfortunately, its not the exact one as I can't post that here! What I am trying to do is to add a new patient record using information from the other tables as guides - e.g. the user selects the name of the Ward but the ID in the Ward table is entered, the User selects the number of the room but again the ID in the Room table is entered, and for the Condition same thing. So I would like to have dropdowns (or ComboBoxes) for each of these and based on the user's selections add a new record to PatientInfo using the ID values as links.

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

Adding New Record To All Tables For 1 Table Primary Key Entry

Oct 20, 2006

Hi, I have 7 tables in my database and 1 form corresponding to all the fields in these tables (linked by a query by recordsource). My problem is that the form will only show a record if ALL 7 tables have manually had the primary key entered (not good when i have information that needs to be added at different times). How can I make it such that if I create a new record on the form that all primary key fields will be updated, and this record will be present every time i open my the form??

Cheers

Tania :o

View 2 Replies View Related

Forms :: Adding A Record To Table From Input Form

May 29, 2014

I have a table with 2 fields in it - Type and Pallet_Case - using a form the user enters the name of the Type and then from a combo box selects either Pallet or Case. There is a button on the form that is to save the new record to the table. (code below) The strangeness that is happening is that when the new record is added to the table, whatever is the first existing record alphabetically in the table is changed to the same Pallet_Case selection that was made for the new record.

I have a list box that displays on the form that is updated after a new record is added to show what records exist in the table (this is done by a query of the table and sorts on the type), so I can see this happening without having to open the table. It doesn't matter what sort order I put the list box in nor the table it still changes the first record alphabetically. And it is only change the Pallet-Case field. I even changed the first record to something else and it still did this.

Dim dbs As DAO.Database, strSql As String
Set dbs = CurrentDb
strSql = "Insert into [PP Type TBL] ([Prepack Type],[Pallet Case]) Values ('" & Me.Type & "','" & Me.Pallet_Case & "' )"
dbs.Execute strSql, dbFailOnError

View 5 Replies View Related

Modules & VBA :: Adding New Record To Related Table In SubForm

May 13, 2015

I am currently working on an Access Database that houses our security clearance information. Most of the system is up and running but the most recent form has got me spinning my wheels. I have a Word User Form that users will download and complete, once completed the macros will automatically send us the document to be added to our database. Most of this is working the problem is that this portion of the database has multiple related tables and at any given time a user may require multiple records in the related tables. I have created the code to copy most of the information but am getting stuck adding a new record on the sub-form when multiple items are required. Here is a breakdown of the scenario

Word Doc Table 1 = Basic Organization Info
Word Doc Table 2+ = Sites to Visit (There could be more than 1 table added here)
Word Doc Table 3+ = People to go on site (this might not be the third table based on user interaction for sites)

So far I can get Table 1 and Table 2 data but if there are more than 1 site I cant seem to get the system to create a new record on the related table it is creating a new record on the main form. Here are the lines I used to try to create the related record..

Me.RFV___Agencies1.SetFocus
DoCmd.GoToRecord , , acNewRec

When I put just the above code on a button it seems to have worked as the sub-form showed an additional record was created but when using this on my macro the sub-form is not taking the focus for some reason.

View 1 Replies View Related

Forms :: Adding Sequential Alphanumeric Record To A Table?

May 18, 2015

I have an Access table with records that have a two letter, two number alphanumeric identifier (AA01, AA02 etc)

I am trying to create a form whereby, when the user types in the first two letters, the next sequential number from the table is generated.

The letters can be totally random and selected by the user, but the numbers do need to be sequential.

View 1 Replies View Related

How Would I Go About Adding A Field Or A Table?

Oct 2, 2005

The attachment is the database Im working on and I have a problem.
I need to add a supplier Product number in there some where so I could keep the numbe as reference and so it may be easier to enter items recieved.
in the form section, I will have Invoice with a sub form Invoice details.
I need to put suppliers Item number somehow so it would search and if the item is on the product list it would fill out the rest of the values.

should I make a separate table for suppliers product number or should I just include that into the invoice detail? I dont want to put it in the product table since I get same Items from different suppliers.

View 1 Replies View Related

Forms :: Adding Field To A Form Breaks Entire Database Record Source?

Sep 26, 2014

I am building a database where one Form displays records from 14 different tables. For some reason, when I recently try to add a field on to a form from a new table, the ENTIRE form loses the record source, and every single field that is already on the form gets that green dot in the corner with errors surrounding a record source that cannot be found. What am I doing wrong? Am I exceeding some limitations with forms?

View 4 Replies View Related

Modules & VBA :: Adding Record To A Table - Closing A Form Without Saving

Nov 26, 2013

I have a form to add a record to a table. How can I give the user a way to close this form without saving the just created record.

I tried

If me.dirty then
DoCmd.RunCommand acCmdDeleteRecord
endif

This works, but gives a messagebox in return to confirm the command, and I don't want that. And I try to avoid sendkeys. I also don't want to change the options of access.

View 2 Replies View Related

Field In Pivot Table Field List Not Shown

Oct 27, 2006

Hi

I am using Access 2002 SP1 and have created a pivot table based on a query which works fine. However if I change the structure of the underlying query by adding another field, the field is not availabe in the Field List in the pivot table. I have used the refresh option but I understand it only applies to the data.

Any ideas?

View 1 Replies View Related

Forms :: Code To Pick A Field From A Record Selected Via List

Dec 11, 2014

I have a query with the following structure;

aDate(pkey) cost1 cost2 cost3 calc1 calc2 calc3
01/012014 ,,,,,,,,,1,,,,,, 2,,,,,,, 3,,,,,, 1,,,,,,, 2,,,,, 3
01/02/2014 ,,,,,,,,1,,,,,, 2,,,,,,,, 3,,,,,, 2,,,,,, 4,,,,,, 6
01/03/2014,,,,,,, 1 ,,,,, 2,,,,,,, 3,,,,,,, 3,,,,,, 6 ,,,,, 9
01/04/2014 ,,,,,, 1,,,,,,,, 2,,,,,,, 3 ,,,,, 4,,,,,, 8,,,,,, 12

fields calc1,2,3 are running totals of cost1,2,3

I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.

result = sum(cost1) - calc1 selected record value

I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.

View 4 Replies View Related







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