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

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

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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

View Replies !   View Related
Cant Create Image Edit Control In Access Form

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


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?


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


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?


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

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.



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

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


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

End If

Exit Sub

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

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
Can't Edit Record On Access Data Page
I am trying to share data across the web using the "pages" function within Access. I can create an associated HTML file and use the navigation buttons to scroll through my records (directly from a table).

In my application I need to edit the data displayed in the HTML file. When I come to try and change the data in the HTML file (and also the page) I get an error message. The data seems to be read only.

I have looked at the Northwinds example and I know what I am trying to achieve is possible. I have also tried to copy all these settings across into my db but I've reached a deadend.

I am sure there is a setting but I am struggling to find it.

Any help would be appreciated.


View Replies !   View Related
Select Default Record > Edit > Save
What I want to happen is ... in my order form

display a default record. ( a record of all products with suggested unit quantity
allow editing of quantity
then be able to save as a new record.

do I need a macro for this ?

any help welcome


View Replies !   View Related

Copyright 2005-08, All rights reserved