Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Sponsored Links:

Related Messages:
Using A Form To Add A Record To Table
I have a table of information. I have created a form to input all the data necessary in order to add a record to the table. The form is not currently based off of the table and is simply a bunch of text boxes with labels.

The reason why I didn't create the form based off the table is because I don't want all the contents of the table to be displayed when the form is loaded. I want to form to be soley for adding new records to the table. I also don't want the form to display any information from the table when it loads, I want it blank. I don't want any chance of the person that is using the form to be confused or accidentally be able to edit the data in the table.

I have added a button that I want to use to initiate adding the new record.

How can I now retrieve all the data in the textboxes and use that to add a new record to the table. Is there simply a few lines of code that can do this that I can add to the on_click of the button?

View Replies !   View Related
Copying The Current Record On A Form To Another Table
is there an easy way to copy the current record on a form when clicking a command button such that a snapshot of that record is copied to a table for archiving purposes?

the table has about 120 columns so it is cumbersome to write an Insert Into sql statement within VBA code.

what i am attempting should be quite straightforward...i just want to take the current record in its entirety that is from a single table and append it to another table of the same structure.

any help would be appreciated!

View Replies !   View Related
Missing Record - 325 In The Table, 324 Showing On The Form???
I have a database which has one main form linked to a table which has 325 records in it. The problem is when you open the form it says there are only 324 records :confused:
I have gone through the table comparing the records with the form records, I found what I thought was the 'missing' record. But when I did a find on the form I managed to retrieve the record.
Can anyone help as I'm a bit baffled.


View Replies !   View Related
Form To Find Record And Move To Another Table
I'm trying to make a form with a single text box that will search for the contents of the text box in a table. Once it finds a match, it moves the record into another table. Anyone know how to do this?

View Replies !   View Related
Multiple Record Entry From A Form In A Table
Hi All,

I am coding in VBA and use Microsoft Access as the front end.
My question is : I have a table(tblStudent) which has two fields Student Name and Gender.

I want to add multiple records in this table through a form at a button click....(Say I want to add least 5 students at a time)
So, I have 5 textboxes for student name, 5 textboxes for Gender and an Add button.
How do I so this programmatically?

Any help will be appreciated.

View Replies !   View Related
Update Record In Table Then Show In Form
ok this is a little confusing so i'll first explain the idea. when we enter a new record (job in this case) it gets given its own job number (prikey), we also set who the customer is for the job.

what i'm trying to do is if we get a repeat of an old job, we enter in a txt box the old job number, it gets that information and when it shows up the new job just entered it is exactly the same as the old job except for the new job number. the other option is repeat with alts which is only a few things different.

ok, hope youre with me...

lots of the original code is in here, done by someone else. i dont want to touch that as it works fine. myset2 is the one i'm using for the old job. myset is closed then reused to get the new job that gets added in. i assumed it would be just myset = myset2 with the field names. if i query these as it runs they bring up the correct info. however they dont seem to get updated in the table ever. i can look at the table after the .update and there's no change.

heres the code:

Code:Dim MyDB As Database Dim MySet As Recordset Set MyDB = DBEngine.Workspaces(0).Databases(0) ' added by aaron 19-10-05 ' extra db for getting old job for repeat options Dim strNewJob As String Dim MyDB2 As Database Dim MySet2 As Recordset Set MyDB2 = DBEngine.Workspaces(0).Databases(0) ' set the second record set if need to to other job info If Me.txtRepeat <> "" Then Set MySet2 = MyDB2.OpenRecordset("Select * from Job_details where job_type = " & Forms![new_job]![job_type] & "AND Job_number = " & Me.txtRepeat) ElseIf Me.txtRepeatAlts <> "" Then Set MySet2 = MyDB2.OpenRecordset("Select * from Job_details where job_type = " & Forms![new_job]![job_type] & "AND Job_number = " & Me.txtRepeatAlts) End If Set MySet = MyDB.OpenRecordset("Select * from Job_details where job_type = " & Forms![new_job]![job_type]) MySet.MoveLast If MySet.RecordCount = 0 Then Forms![new_job]![Job_no] = 1 MySet.Close Else Forms![new_job]![Job_no] = MySet.[Job_number] + 1 strNewJob = MySet.[Job_number] + 1 MySet.Close End If Dim docName As String Dim strCriteria As String Dim strRepeat As String ' flag repeat cases If Me.txtRepeat <> "" Then strRepeat = "Straight" ElseIf Me.txtRepeatAlts <> "" Then strRepeat = "Alts" End If Me.Refresh 'Changed to not use macro - 8-Mar-2005 thisType = Forms![new_job]![job_type] docName = "Q New Job" DoCmd.OpenQuery docName, A_NORMAL, A_EDIT DoCmd.SetWarnings True ' open job, edit, change details, update and close job If strRepeat = "Straight" Then Set MySet = MyDB.OpenRecordset("Select * from Job_details where job_number = " & strNewJob) MySet.Edit MySet("Material") = MySet2("Material") MySet!MaterialInfo = "blah" MySet.Update MySet.Close Set MySet = Nothing ElseIf Me.txtRepeatAlts <> "" Then 'MySet.[Material] = MySet2.[Material] End If 'Added 8-Mar-2005 : Now opens Job form differently by uses new load data function strCriteria = "Job_number = " & Me.Job_no & " AND Job_type = " & Me.job_type DoCmd.OpenForm "Job_Details" DoCmd.Close A_FORM, "new_job", acSaveNo MyDB.Close Set MyDB = Nothing Call Forms("Job_Details").gLoadData(strCriteria) 'DoCmd.OpenForm "Job_Details", , , "JOB_TYPE = " & Forms![new_job]![job_type] & _ "and job_number = " & Forms![new_job]![Job_no] DoCmd.SetWarnings False DoCmd.OpenQuery "Q Delete New Job" 'DoCmd.Echo True DoCmd.SetWarnings True DoCmd.GoToControl Forms![Job_Details]![Contact].Name

i hope someone can understand that, its taken me a while to see how it works. i thought it'd be an easy tack on, hasnt been the case! so if anyone can help me with the update part, that'd be great


ps that = "blah" is just for testing obviously... that didnt even get into the table either.

View Replies !   View Related
How To Add A Record In A Table Based On NotInList Option In A Form
I've 2 tables:



I've a form to create new customers accounts.
The form has a drop-down control listing the cities in the "cities" table.
This drop-down control has a hidden column (id) and a visible column (city_name).
The user can choose a citie from the list and the city id (1st column in the drop-down list)
will be stored in the "city_id" field in the "customers" table.


I need to allow users to enter cities not listed in the drop-down control.
When they entrer a new city, I need to create a new record in "cities" table for the new city.

I tried opening a dialog using the NotInList event, and the dialog pops up as espected, but also pops up an ugly msgbox wich says that the typed text isn't a listed item.

Please help :-(

View Replies !   View Related
Table Record Entries Exists But 0 Shown In Form

In the current db there are some tables and queries, forms designed around them

With one table Test

That has 3 records in them

Just won't show in form anymore, at this stage

The Form was working previously. This is the second time this problem has came up, I thought it would go away if I made a new form and pasted all the controls and codes. It indeed went away for a while until this problem hit me again...

This db is in Access 2000 format and designed using Access 2003

I would appreciate if anyone is willing to point me to the right directions, many many thanks in advance

View Replies !   View Related
First Record In Table To Appear In Unbound Text Field On Form?
Hi All,

I hope someone can help me.

I have a table here tblTemp with a field client. This table can have from 1 to 100 records at times as it keeps getting cleared for the purpose of this exercise.

I also have a form frmLabel1Data with an unbound text box txtclient.

I was hoping to get the first record of the table into this text box upon opening the form.

I tried creating a query which gave me the following sql;

SELECT First(tblTemp.client) AS FirstOfclient FROM tblTemp;

I also tried placing it in this code with no luck

Private Sub Form_Open(Cancel As Integer)


SQL = "SELECT First(tblTemp.client) AS FirstOfclient FROM tblTemp;"
MsgBox SQL

End Sub

Does anyone know why I cannot see the same result as when I open the query it gives me my client name but the code above does not????

Any help is appreciated.


View Replies !   View Related
Updating Table Primary Keys On Form Load For Last Record
Hi, Before i even open a form I have exported data from another program into the main table and one other table (5 in total). I need some way of looking at the last record in the main table and setting the primary key of three other tables equal to this one...This is what i have been working with at the moment, though movelast is giving error on compile:

rivate Sub Form_Load()

Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()

Set RS = db.OpenRecordset("Patient_Information", dbOpenDynaset)
Set LastPatientRecord = RS.MoveLast

Set RS = db.OpenRecordset("Test1", dbOpenDynaset)
RS![HospitalNumber] = LastPatientRecord

...then i would repeat this for two other tables
End Sub

any advice wold be appreciated!!

tan :-)

View Replies !   View Related
"You Cannot Add Or Change A Record Because A Related Record Is Required In Table....
Hi! Please help!!!

I'm currently building a bookings database and have encountered an alert message that I cant seem to rectify -

"You cannot add or change a record because a related record is required in the table 'Booking Details'"

Basically - I have a 'Customer Database' form that is linked (via command button) to a 'Booking Details' form. Within 'Booking Details' I have 2 sub forms - 'Booking Quote' and 'Booking Payments'. Both subforms are linked to the 'Booking Details' form by the 'booking ref' field with RI.

I have no problem updating information in the 'Booking Quote' subform, but when I try to add information to 'Booking Payments' it states the above message.

Can anyone please advise as to how I can prevent this happening? I'm slowly losing my mind....!!!

Many thanks,


View Replies !   View Related
Lose Record Edit Via Form; Input Continues As New Record
PROCESS AS DESIGNED: The user logs in and is given a form displaying existing records that are coded (by userid) for that user to update. From that list, the user selects a record and continues to a second form (the first form closes) which displays all the fields of the selected record which should/can be edited by the user (plus a few non-editable key fields). There is nothing tricky to how the records are selected for display. A simple query behind the first form selects data records using the userID number. The second form does a simple record select using the active record ID number on the first form. Nothing tricky -- no modules just queries, tables, forms and simple macros to string it all together.

The user can open/close these two forms as often as necessary and the record will continue to show up until a value is entered into a "completion date" field. When that field is no longer blank, the record will no longer be displayed for use.

PROBLEM: About 90% of the time, things work as expected. But 10-15% of the time, somewhere between opening the record in the second form and closing the second form, the edited record totally disappears (as if it were never opened, it isn't even system date stamped) and the input information is inserted instead into a brand new record.

Users can sometimes tell when the edit record is dropped during processing, because key fields displaying previously entered data go blank and the displayed recordID (autonumber) is changed. p>
ERROR PATTERN: NONE! It can happen as the edit record is first opening, in the middle of editing the form, or at the end when closing the form. It will happen on a specific PC 2 times in a row and the third time be fine. It has happened at multiple locations but not for all people and not consistently for the same person/same pc. The same record that failed can be re-selected and work just fine the next time.

I cannot reproduce the error myself. I have tested everything I can think of without causing the error. I can lose changes and avoid a system date stamp if I abort my Access sesssion, but that doesn't create/insert a new record with my new input. It just loses everything.

I have wondered if a momentary break in the network/communications could break the connection to the application to just enough that the form remains on the screen, but selected record is discarded. Input into the empty form would/does insert a new record.

I have asked the IT Operations group to investigate if a network/communication problem could be the culprit, but they haven't gotten far on this. I have never seen this happen before and am totally stumped but am desperate to resolve it before user confidence is damaged.

View Replies !   View Related
Cannot Add Record To A Table Because Related Record Is Not Recognized
Hi everyone,
I am using Access 2007 and I have a database with 2 tables. The original table has a primary key field designated. I have a relationship between that table and a second table and that relationship has "enforce referential integrity" checked.

In the secondary table the field that the relationship is bound to is not a primary key but has the same field properties.

I created a new record in the original table and created the primary key of "T-124". When I attempted to add the same data to the field in the second table, I recieved the following error message: "You cannot add or change a record because a related record is required" in the other table.

I did create the record in the other table:eek:

Can anybody help????:(

View Replies !   View Related
Move Record To New Table After Data Change In Combo Box Value In Original Table.
Ok my problem is this......

I have a form with a field "Property Status" on it. It has 4 possible values -

"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"

When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.

Any ideas would be greatly appreciated. How would this be coded?

View Replies !   View Related
Need Form For New Record/edit Current Record
I have a table called tblFinishedGoods. There are 3 fields in the table:Serial Number, Model Number and Location.

I would like to have a form that will take input from my barcode reader and input the data for the 3 fields, if the serial number is not already in the table.(I have this already working).
But now I would like the same form to also check the table for any serial number that is scanned in...and if it is in the table already, have it bring up the model number and location for that record.

I'm thinking maybe there is something I can do with Serial Number afterupdate. Basically I'm looking for a way to not require the user to input anything via a mouse or keyboard.

If the 1 form could accept the 3 scans:Serial Number,Model Number and Location, and either 1)enter a new record if that serial number isnt in the table or 2)find that the serial number already exists and overwrite the model number and location with whatever the next 2 scans are.


View Replies !   View Related

Copyright 2005-08, All rights reserved