Create Record With VBA, Edit With Form

I have the following code on a combo box in a form that creates a new record in the table Products if it doesn't already exist:

Code:Private Sub comProduct_NotInList(NewData As String, Response As Integer) Dim strSQL As String 'Exit this sub if the combo box is cleared If NewData = "" Then Exit Sub strSQL = "Insert Into Products ([Product]) " & _"values ('" & NewData & "');" CurrentDb.Execute strSQL, dbFailOnError Response = acDataErrAdded End Sub

It creates a new record and inputs the appropriate value into the Product field, however then it goes to the next record and when I try to edit other fields, it does so on a new record.

So, for example, I wanted to set the Product field to ProductA and the Brand field to BrandB and the Size field to 200, it produces two separate records like this (code box used for formatting):

Code:Product | Brand | Size |ProductA BrandB 200



ADVERTISEMENT

How Create Form That Allows You To Edit But Not Add New Records.

I guess this is a fairly basic one. I have a form that allows people to edit records, but I want to prevent people from adding new records on this form. I thought that if I put 'no' in the 'Allow additions' property, I would achieve this end, but when I do this, all the input boxes dissappear.
appreciate help
thanks

View Replies View Related

Cant Create Image Edit Control In Access Form

Hi,

I've searched a lot and couldn't find an answer to my problem.

I want to have a field in my form that allow me to paste an image from the clipboard to, for example, a Paint field. Then, I want to save that image to a file on a specific folder in my computer via VB.

The reason for this is that I don't want to ask the user to open paint by himself and save the file, probably in the wrong location and with the wrong name.

The first approach was creating an OLE Object directly from the Insert menu and that worked partially. It allowed me to paste the image but there wasn't a way to save it to a file, neither a method for that. It was an incrusted object, and I think that is the reason for that.

Then I tried to create the object by myself via VB, with the instructions:

Dim objeto_perfil As Object
Set objeto_perfil = CreateObject("Paint.Picture") 'IT STOPS HERE

And with the variation:

Dim objeto_perfil As Object
Set objeto_perfil = CreateObject("Paint") 'IT STOPS HERE

And neither worked. The error message says it lacks an object.

Then I followed an example from msdn that used a bound frame (view here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acproClass_HV05187062.asp)). The code is:


visor_perfil.Class = "Paint.Picture" ' Set class name.
' Specify type of object.
visor_perfil.OLETypeAllowed = acOLELinked
' Specify source file.
visor_perfil.SourceDoc = "C:RealPathimage.BMP"
' Create linked object.
visor_perfil.Action = acOLECreateLink 'IT STOPS HERE
' Adjust control size.
visor_perfil.SizeMode = acOLESizeZoom


It stops in the action assignation, an says "error 2753 there was an error trying to communicate with the OLE server".

I google a lot and could find much of the Paint.Picture Class.

Anyone has any ideas to overcome this issue? What am I doing wrong?

Thanks in advance!

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

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.

Thanks

View Replies View Related

Searching Particular Record To Edit In Form

I have a form that assigns an Auto Reference Number to each record. Now the records are later edited. Is there a way that I can have the user enter the Reference number and come directly to that particular record that needs to be changed in the form. Like a dialog box that asks Enter Reference Number or something.

View Replies View Related

Edit An Exisiting Record From A Form

I'd like to set up a form that allows you to enter a unique number for a record and then automatically change the value for one or more fields in that record.

I have a large number of records that I only need to add values to two fields so am looking to make a shorter form with the ultimate ail of doing this with a barcode reader.

I thought I could just add a line like:

Me.controlvaluetochangename.Value = "newvalue"

to and event property like after update and it would change the name for that record. Instead it created a new record with that value.

How do I go about changing the existing value?

Thanks

View Replies View Related

Opening Edit Form At Certain Record? - Version: 2000 (9.0)

Hi there, just a quick question.. I'm opening up a form to edit my DB from a drop-down record ID selector (the intention being to quickly open records I've checked for null values to be completed). Anyway here's the line I've been using to open the form:

DoCmd.OpenForm "Faults", acNormal, , "[Report ID] = " & Forms![recselect]![Comboval]

This is OK, it opens up the correct record - but filtered. Any way I can get it to go to the selected record while still having the entire set display?

Thanks for any advice in advance

View Replies View Related

Last Record - Continuous Form Edit - Version: 2002 (10.0) XP

I have a continuous form which displays all records being edited. My problem is when I reach the last record on the form,once I make my edit, the form won't allow me to "tab" or "enter key" out of that cell. In other words, when I reach the last record it can receive focus but can't lose focus unless I arrow key upward. This is causing that last edit value not to get saved. The CYCLE property is set to All records. How do I get the enter or tab key to work for that last entry ?

View Replies View Related

Intercepting Record Insertion/edit On A Form - Version: 2000 (9.0)

How could I programmatically stopa record insertion (or reverse an edit operation) on a form using VBA?
Would such things as "rollback" and/or "cloning recordset" work? and how? Please, I need Aid!

View Replies View Related

Random: Record Edit Via Form Drops, Input Continues As New Recor - Version: 97 (8.0)

I have a networked application that is doing STRANGE things.
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. Any ideas are welcomed!!! Thanks.

Karen - karenk10@swbell.net or karen.kauder@mhmraharris.org

View Replies View Related

Create And Edit Image In Access

Hello! All,
I need to store a customer signature in the database so that we can take a print out at a later time.
I don't know how to go about doing this and thought that creating and editing an image within an access form should be a good option. But I don't know how to go about doing that either. So could anyone please help with this and also if you have a better idea, could you suggest that too. I have the application running on a tablet PC so there's something called Stickynotes too that comes with it. It would be great if someone could suggest a solution

Rishi..

View Replies View Related

Create New Record With Form

How do I create a new record (in the table to which the form is bound) automatically if, when the form opens, there is no record in the table that meets the criteria in the form filter?

Thanks!

View Replies View Related

Use Access To Open Word, Create A NEW Document, And Then Edit The Doc

I want to create a new word doc, do a saveas to a specific path and file name, allow the user to edit the new doc, and then close it and return to the Access app. I can create the doc but Word opens without opening the New Savedas Doc. using this code

Dim oApp As Word.Application
Dim WordDoc As Word.Document
Set oApp = CreateObject("Word.Application")
Set WordDoc = CreateObject("Word.document")
oApp.Visible = True
WordDoc.SaveAs ("TS-" & Me.Scriptnum & ".Doc")

Your help is appreciated.

View Replies View Related

Linking Form To Search Or Create New Record

I have uploaded an example of my database

As you can see there are two tables and two forms.
What i want is for a user to view or enter data for a record on the first form.
And then when they are done they click the button to open up form2.
What i want to happen is for the company name/id from the record in form1 to be searched for when form2 opens and to display only the data for that record when opened. If no data exists for that record in form2/table2 then for it too create a record for that company when form2 is opened from form1

hope this makes sense and you can help

Please note this is just an example so if there is wrong naming conventions and such please ignore if there is as i just wanted to display quickly what i want to acheive

View Replies View Related

Simple: Create A New Record, Update Old Form

Hi... I searched quick, didn't find anythign, but you guys are so responsive... :D

Question:
Part 1. On TimeSheet form, I want to click to "create new job", and the Jobs Form opens, at a new record. Currently it opens at record 1.

Part 2. I foresee that when I return from the modal job form, the old form (timesheet) would not have updated the combobox to show the jobname I just entered.

I guess this is what subforms are, because I keep reading about them, but dunno what they are.

Thanks

-Reenen

View Replies View Related

Create New Record On Form And Subform Simultaneously

When I click the new record command button which I created I get a new record, but the form has a subform and this starts with no records. How can I program this button to not only create a new record and the form to which it relates, but also to create a new record on the subform contained within it as well??

View Replies View Related

Create A New Record, Opening A Form Based On The Key Field

I seem to need some help!
I have a table with customer information in it ie: name, vehicle, (Key=Id number)...
it is the master link to the repair table ie: mileage, (Key=ROnumber), and repair data..
these two work together beautifull
now I need to add another table, a check sheet for checking over a vehicle.
I create the table and the form however I cannot open a new form with the ROnumber from the second already open form into the newly open form.
(the second form is based on a Query, and I have tried changing it to a SQL statement both with no luck)
is there an example of what I need to be doing to link the ROnumber to the ROnumber in the new form, or create the ROnumber so it will add the data in the linked table?
I need this to open using a button on the open repair form
David

View Replies View Related

Create New Record And Open Form - Version: 2003 (11.0)

I have an item record displayed in a form. I have a "Billing" button. When the button is pushed a new form opens and the user creates a billing record and inputs the billing information. This is code in my OnClick event.

stDocName = "FrmCustDetailBilling"
stLinkCriteria = "[CustDetNumber]=" & Me.CustDetNumber
DoCmd.OpenForm stDocName, , , stLinkCriteria

The "FrmCustDetailBilling" form has "DataEntry"=Yes. When the form opens, a new record is created, but my ID number is not copied over. I need it to have the ID number of the item that was on the first form.

Help? Thanks.

View Replies View Related

Edit A Record, Keep New And Old Record In Same Table. - Version: 2003 (11.0)

When I enter a form and look at a record, it shows the latest record for an employee. Right now, if you change that record, it changes the record in the table. I want to keep the old record the same after I make changes to it through the form and save what I changed as a new record.
Is there an easy way to do this?

View Replies View Related

Using Form Info To Create A New Record In A Different Table - Version: 2000 (9.0)

I thought I would write a "simple" little MS Access program for our local animal shelter to use to keep track of the medications and shots they need to do for the dogs. Something they could enter data into and run reports to see what meds needed to be given on any given date, then keep record of that meds actually haven been administered. I have a system that they are using quite well. However, I want the staff to be able to enter the start date for meds and to have the subform (Medical Schedule) populated (I would actually settle for just getting the entires in the medical schedule table) Right now they have to enter every med for every dog by hand and this is error prone, easy to miss a standard med or mistype a date.

We have a date pattern and standard meds we use: shots every 2 weeks for 4 shots if dog is under 8 months, for 2 shots if over 8 months. Besides shots, we give another vaccine, wormer, and heartworm prevention the same day. 6 days later we start a 3 day series of wormer and 10 days after the last day of that series we do another 3 days. So all the necessary data can be genreated given a start date. I have attached copies of the pertinent table definitions and the form view.

I need to know how to pick up the DogID of the dog record being created, how to build one record for the Medical Schedule (calculate the date, build the record and save in the schedule table). Since we have a "schedule template" I was planning on "hard coding" the meds since I know the IDs in the Medicines and Tests table. A bonus would be to know how to pick up the age qualifier and age to covert age to weeks so I can calculate if 2 shots or 4 are needed and when rabies is needed (at 12 weeks)

I am pretty good at plagiarizing code but have not been able to find any examples that cover what I am trying to do!!!!

The big problem is that I want to put in a start meds date and have the medication schedule calculated in an event procedure and don't know how to build the records or add, insert, save, whatever in the medical schedule table. It seems forms and subforms deal with individual tables and won't combine data from multiple tables.

I think if someone can show me how to get one record built and saved, I can figure out how to modify that to get all the rest done.

Any guidance is appreciaed by myself, the staff, and the animals. Check us out at www.prcspca.org

PS - If you have not figured it out, I am new at forums. I have attached screen prints of the table layouts and form view. My mdb is 1028KB and I think that is too big for me to post.

View Replies View Related

How Do I Create Macro Or Module To Record Information On A Form? - Version: 2000 (9.0)

Hi,
I am writing a database that has a form that populates a table in Access 2000, i am looking to run a macro or module or bit of code in the background that records all entry information by users that input information into the form. Is this possible and does anyone have any code examples?

View Replies View Related

How To Create Form To Show History Of Changes Made To A Record - Version: 2003 (11.0)

I have a form called Built Items with a subform called Raw Materials Received which shows all the raw materials it takes to compose the built item showed in the main form. I have 6 fields in the subform (Raw Material Item #, Date, Vendor, Inv #, Cost, Cost w/ Freight). Each record in the main form will have a set of records in the subform where the Raw Material Item #s will always remain the same, but the Date, Inv #, Cost, Cost w/ Freight will change every time one of the items is received.

I need to create a form that has a record for each Raw Material Item # with a subform that shows the history of the changes made to the Raw Materials Received subform. So basically, every time I update the information of a record in the Raw Materials Received subform, it will automatically add the record (before the change was made) to the history subform, and do so whenever a change is made to any record for that specific item.

Hopefully this isn't too confusing and makes sense to someone. I appreciate any help on this!!

View Replies View Related

Open A Form And Filter To The Current Record OR Create A New One - Version: 2007 (12.0)

Hi

I'm fairly new to Access and I've been bumbling my way along for sometime but now I'm stuck.

I have two tables, a 'Pupil' and an 'Attend' table, with respective forms, in a one-to-one relationship. The Pupil Form displays the corresponding record from the attend table, but to edit this record you have to open the attend form. I don't want the user to be able to edit the attend table record from the pupil form.

I understand how to use a command button to open a new form from another form and filter the new form based on a control on the initial form (which I learnt from here - thanks guys!). However, when their is no corresponding record in the new forms' control source, how do I insert that record into the new forms' control source? E.g. When looking at John Smith in the pupil form and I open up the attend form using the command button, I want John Smith's record to be inserted into this form if he doens't already have a record. Or am I completely misunderstanding how such things work?

I'm a complete novice at using SQL and macros etc.

Any help would be much appreciated - even a point in the right direction - thanks

View Replies View Related

How Do I Create A Record Lock Button/toggle In Access Form - Version: 2003 (11.0)

Hi

I have a dataabse that many users connect to and work on. It's a simple survey app. However, I would like to create a toggle switch that when clicked locks that particular record, so that it cannot be edited by the other users.

I have tried to create something by usin the Form_beforeupdate event, and having a toggle switch called lockbool.

Only thing is once i lock the records, all records are locked, and i can't untoggle the switch because it's locked.

Any ideas or Links

Thanks

View Replies View Related

Select Record/ Or Edit Record

Hi There,

I have a form 'Players' which has a field called Contact. When adding a new record to the Players form, the user has to select an existing contact or add a new one (they cant just type in a name).

(There is a contacts table)

So, i want a button next to the contacts field.I want it to:

1. If there is already a contact selected on the form, the contacts form open at this contact, so can be edited.

2. If there is no contact selected, the button will take the user to the contacts form, but will display the first record in the contacts table. From here the user can select the contact they want.

This is what i have (code below). With this code, if there is a contact already entered, the contacts form loads with that contact select, which is correct. However, if no contact has been selected, the contacts form opens with a blank record; whereas i want it to open at first record!

Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmContacts"

If Me.Contact = " " Then

DoCmd.OpenForm stDocName

Else

stLinkCriteria = "[Name]=" & "'" & Me![Contact] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

End Sub

View Replies View Related

Edit Current Record

Hi All,

I Built a form with a selectbox. On each form there a several records shown. After each record you see a selectbox wich you can turn on and off. Now, everytime I click on a selectbox it automatically goes to the first record on the screen, why is that? With this action I want the currentrecord to be modified, but can't figure out how.

I think it has something to do that an 'edit mode' is selected.

Does anyone know how? Thanks in advance.

View Replies View Related

Lock Edit Record

Hi,

I'm new user here and I use Microsoft Access 2003 for my applications.

I use an mdb on a server (datas) and form on station over the network (application).

I would like to lock the record edit by user when this user press "modify" button and unlock the record when this user press "save" button to avoid with the other users modifying the recording.

Is it possible and how ?

Thanks for your answer.

View Replies View Related

Another User Did Not Edit This Record!

I have a form based on a query that selects only the people working in a single office, and displays
various personnel data items. All of the data is contained in a single table. Whenever I update
any of the data in the form, I get a message saying that another user edited and saved the data
before I edited the data. Its rother annoying, and I can't figure out why it is happening.
The form is bound to the query, and a list box contains the names of the folks. A subform contains
the personnel data, and the two are connected by the primary key.

View Replies View Related

Unable To Edit Record

Hey Guys and Gals,

I have a form that has a drop-down box. When the form opens, all the records are shown. Once a name is selected from the drop-down, only the records associated with that name are shown (a filter is created using code). The problem is that once the selection is made, I am unable to edit the records. I try to click in the fields but am unable to.

Any ideas? Thanks

View Replies View Related

Can't Edit Subform When Adding New Record

I have inherited a partially completed membership Access database which I
understand was originally generated from a load of linked FoxPro tables.
There is a form which uses multiple tabs, the first tab contains general
membership data (ie name, age etc) and this pulls data through from the
'Main' table in the database. Each of the other tabs includes a subform which
shows data held in a number of different tables linked to the 'Main' table
(eg, first tab - general data from 'Main table', 2nd tab - home addreses
from 'Home Address' table, 3rd tab - business addresses from 'Business
Address' table). All tables are linked to the 'Main' table through a common
'RegisterID' field. When I open the form I can query on any of the records
and amend and items of data on the various tabs them without any problem.
However if I add a new record using the >* option from the record navigation
I can add data to the first tab (ie. into the 'Main' table) but if I move to
the 2nd or 3rd tab I am unable to edit/add data to the fields on those tabs.
But if I save the record after completing the fields on the first tab and
then close the form and reopen it I can then edit the 2nd and 3rd tabs, can
you suggest how I can change the setup to allow editing/adding data to the
2nd and 3rd tabs without having to close the form and reopen it please. I
have checked the subforms and they are set to AllowEdit and there are NoLocks.

View Replies View Related

Edit Only One Record - Version: 2003 (11.0)

I have a continuous form with five fields including a Y/N field called EDIT. When the form opens, all fields except EDIT are disenabled. If I click the EDIT field, it enables the others. However, if I click any records EDIT field, all records are enabled. I only want the field I checked enabled (or editable). Is this possible?

Thanks,
SKK

View Replies View Related

Edit Record With ADO - Version: 2000 (9.0)

Hi!

Anyone got an example on how to edit a specific record with ADO?
I can add and delete records, but don't know how to edit

HKM

View Replies View Related







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