i have an unbound form with the following code which works as it should
Code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tbl_Courses", dbOpenDynaset)
With rst
.AddNew
rst!DateCreated = DateCreated
rst!Originator = Originator
[code]....
I also need to add other text fields from the unbound form to another table but can't seem to work out how to do it.
Hello, here is my problem:
I have a form to add records to my table. I cant bound it, because the controls are tick boxes, and they need to be "converted" into text to add them to my table.
I have a save button, so I can execute an insert statement with all the data I want to add to the table.
The problem is, once I have saved the record, I want to clear the form to add a new record, and I cannot use a docmd.gotorecord acnext as it is an unbound form.
Is there any way to clean a form without going control by control?
Thanks!
Cristina
I have two tables that I want to update from data in an unbound form. How do I capture the ID of the new record created in table 1 in order to write it to a field in the new record being created in table 2.
Table 1
id
f1
f2
f3 and so on
Table 2
ID
Table 1 id
F2
F3 and so on
I total novice at VBA. I am trying to code a button to modify (the last) record in a subform list and then add a new record based on values in unrelated or unbound fields on the button form.
The following code is based on the first of two YouTube tutorials (this bit on the edit) and looks like it should work. Except that my Access 2010 with Visual Basic for Aplication v7 does not recognise the type definition Database or Recordset
Code:
Private Sub ANOwner_Click()
Dim cn As Integer
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
[Code] .....
OK I'm totally lost again.
What I want to do is;
Use one form to add records into several tables.
I have seperated my data into seperate tables and set up one to many relationships.
tblincident
--------------
Key#
Report#
Time
Date
Location
tblPerson
----------------
Key#
Last Name
First Name
Middle Name
Address
etc.
tblItem
----------------
Key#
Make
Model
Serial#
etc.
What i want to do is use one form to enter all this data into the different tables.
There can only be one record in tblIncident that can match multiple records in the other tables.
i.e. for each record in tblIncident, I want to be able to have the capability to have as many persons related to that incident as I need.
I was thinking about setting up a tabctl for the data that has to be entered for tblIncident and then change the property setting for it to go away and then have the next tabctl appear in it's place for entering data into the next table. The problem I am having is that apparently I can only have one table as the control source for the form.
So next I tried using subforms, but that isn't working either. I can't figure out how to make the subform appear in a specific place on my form, sized in specific dimensions I want where I want.
I am just starting to learn about this relationships stuff and it's kind of difficult. How do you make it where you can do this from one form? Do I need to make a query of some sort?
Any help is appreciated.
have set up a navigation form which contains 5 unbound subforms that are made visible or not visible thru the on click event of command buttons on the main form, the subforms have a command button with an embedded macro that searches for a record based on what has been keyed to an unbound text box on the subform, this works fine when the sub forms are opened independently of the navigation form but when the search is done from within the main form, the error message is, "....cannot find the referenced from frmAQIFAList" subform, I tried referencing the main form name infront of the subform name but this also doesn't work, perhaps this would be better with code ? or is it because the subforms are unbound ?
View 4 Replies View RelatedI start with a table of UserIDs, names and roles (tblUserIDList). I have a simple query off that which lists them in alpha order by UserID. Next I have a simple form with a combo box which uses that query to select the User and binds the UserID field to use.
There is a button which triggers a macro which opens the 2nd form using a query which filters using that UserID. There is another point in that form in which that UserID is again used as a filter for other data. The 2nd form is a data input form. All works fine up to that point.
However, when the 2 forms are closed, for some reason the UserID is re-added to the tblUserIDList. I've never had this happen before.
I'm using Access 2007 and building a form with controls that will be used to query one of the tables in my database. To pull back results from this table between two dates I have used the "Add Fields" method to drag and drop two textbox date pickers onto the form from the list of available fields. Both of these controls are bound to the same date field in the underlying table.
Will this cause problems when a user attempts to select two different dates on the form? Will a date on the first control be mirrored on the second control and vice versa? Should I remove these bound controls and add two unbound textbox date pickers in their place?
I also need to ensure that the date selected on the second control is the same or later than that on the first.
I have an existing Main form that has a sub form that the user uses to enter multiple records into a table....it works fine EXCEPT that I need to make it even easier and more intuitive and add a lot of labels. Basically the user selects items from a drop down list that adds items to a Work Order. I need to add some labels to the form to make it more descriptive for the user.
So, what I want to add multiple records using a single main form.
Is is possible to?:
1. simply turn the subform into a single main form? Can this be done by using a Command button or something similar?
2. copy all of the controls etc from the sub form into a new main form and have it all work nicely?
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I have a form that reads records from a query.. It loads the first record into the form, without issue.
Code:
Sub FormLoaded()
Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("Results") ' Query we want
Forms("frmmainnew").lbladdUser1bad.Visible = False
[Code] ....
Now I have have 2 buttons at the bottom of the form, one for next record and another for previous record...
Code for next record
Sub NextRecordbtn()
Dim r As DAO.Recordset
Set r = CurrentDb.OpenRecordset("Results") ' Query we want
r.MoveNext
If r.EOF = True Then
[Code] ....
I know that I have 3 records that result in the query named "Results", the next record button will bring the form from record 1, to record 2... however it will not move from the 2nd to the 3rd record...
Hi there,
I'm trying to add an image into a table. I know I can do it manually by creating an OLE Object and so forth. I am actually trying to get the user to enter in the path of the image and add it into the record/table.
Basically this is for a database that will hold a scanned image of a certificate associated with each person entered into the database. Anyone have any suggestions?
Thanks
I am new to Access here, and looking to start up a database that will contain data for thousands of footballers.
I have on one table ("Directory"), all 5000+ players, with nationalities, names etc, and on another ("Flags"), I have all nations of the world and their respective flags (as an attachment).
How do I add the flag of a players nation into the "Directory" table for each record? Is there a quick way? I've tried "Lookup" but it won't let me do it for images, I've tried "Relationships" but without success.....surely I don't have to go through the "add attachment" process for all 5000 records?
i have developed an access database and working on interface through forms & reports .i needed that a user must have to click save button to update the data and i have found that without an unbound control its hard to achieve functionality .so i want to know step by step how to update records using unbound controls and a save button .
View 6 Replies View RelatedWhat I have is a database that I have done some tweaking on and in the meantime the original db has been in use which has added around 200 or so more records in the table.
What I would like to do is to just update the db that I have been working on with the older db table(the one who has the additional 200 records).
EX. DB A(Old DB, Newer Table) DB B(New DB, Older Table)
I want to put DB A table into DB B
Is this a simple fix? Or do I need to write some sort of query to update the records in the old table? I've tried to export the excel file and then import but it puts it in unrelated objects and then my switchboard or nothing works.
I have an alphanumeric primary key that goes
REF0001
REF0002
REF0003
e.t.c.
When i get to REF9999 and enter REF10000 it does not store it after REF9999 but stores it after REF100. Why is this ?
Is there a quick way that i can add leading 0's onto my records. So it will read
REF000001
REF000002
REF000003
I have three tables
MyForms
MyControls
MyLanguage
There is a 1 to many relationship between MyForms and MyControls
There is a one to many relationship between MyControls and MyLanguage
I have a function that populates these tables.
For each form in the system I store 1 record on the MyForms table.
I then store 1 record for each control in the form on MyControls.
For each control I store N (currently 2) records on MyLanguage.
This all works fine up to a point. I noticed that after a time records were no longer getting stored on MyControls and MyLanguage.
The only way around this was to delete all records on all files (I have cascade delete so only need to delete MyForms) and the Compat and Repair the database. The compact and repair doesn't work unless I delete the records first.
It seems to go astray when the MyControls table hits 14-1500 records (MyLanguage 28-3000).
I've stepped through the code line by line and the store of the records appears to go through fine.
I have form with an unbound combo box which is populated from a query
It should populate a field in a table which is related to another - however it only works when i query by id number (primary key) and not the text value i want - is it possible to query both so it returns say 1,option 1 - then when selected it will place a 1 in the table - which is related to option 1 in another table instead of just having to select '1' which will mean nothing to a user?
Table in my access 2010 is configured to have a unique records (no duplicates)which has now records more than 2000 so i copied the table and pasted Structure only. what i would like to have is that new table which presently is empty should not add any record which is already available in old table. While entering data in new table i would like users to see the error if they try to enter the record which was previously entered in old table.
View 8 Replies View RelatedI have a table with more than 60 fields and need to update it with records from another Access file with a table with an identical data structure.
Is it better to run an update query or an append query (i would have to delete the original records in the target table first) or a union query?
If I run an update query I will have to manually add each field to the query.
The update query will not add 'new' records. If I run an append it is quicker because I can use the * to match all fields, but i will have to delete the 'old' records first, as both tables use autonumber for the PK so the PK ID will be the same in each table (will get a key violation error).
If I import the 'new' table and run a union query it will match the fields and add the new records, but then i will have to create new table from that query.
Supposing you have 4 Tables
1.)User
2.)Admin
3.)Teacher
4.)Pupil
Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.
Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.
Question is: Is that possible? If yes, then how??
I have the logical process, but I just don't know how to put it into code.
I have a form with two boxes HireMovieID and HireCustomerID, with a button HireButton.
The two tables which I have are CustomerInfo and MovieList.
Here's the psuedocode for what I need to happen:
Code:
IF HireMovieID.Genre = "R16"
(IF DOB > Today - 16 years)
Display Error: "Too young"
ELSE Hire()
[Code] ....
I need to somehow make it so that the MovieID is valid, as in, if it doesn't exist then it comes up with an Error MsgBox.
Okay I had an idea and I thought I might get ffedback as I am relatively new to this. I went through a period a couple of years ago when I used access alot and was becoming familiar with VBA etc but I havent touched it in 2 years so Im pretty rusty.
I am running a study and need to have to create a database that:
a. collects data about participants
b. Has a number of questionnaires (5), each of which can be filled out by participants.
THe main table has a number of fields that collects info about the participants the most important of which will be the ParticipantID - an automatically generated number which is my primary key.
In table 2/form 2 I will host questionnaire one. This will be linked to Table 1/Form 1 (Particpant Information) by this tables primary key - also the Participant ID. The relationship will be 1:1. Each participant can only have one Participant ID and will only need to fill out questionnaire one once.
Is it possible that when I add a new participant to the Participant Information table/form I also automatically create a record in Table 2/Form 2 (Questionnaire 1), as well as Table 3/Form3 (Questionnaire 2) and so on so that they have the same ParticipantID...?
I was reading a similar query somewhere else and they said to use the Form_AfterInsert Event command? Is this right (see here http://www.pcreview.co.uk/forums/thread-1687644.php)?
I feel a bit stupid but I am willing to learn and try new things Once I get started I think I will be okay. If you could steer me in the right direction it would be much appreciated.
Using DAO recordset, table enters unbound fields data properly for desired records, but adds an extra record which is a duplicate of the first record entered....any known access quirks for adding unwanted records?
View 3 Replies View RelatedThe unbound Parent form has a listbox (SQL Server) , the selection updates the SubForm with a primary key ID. The Subform is a ReadOnly view from an Oracle Linked table. It populates with a Select * From Oracleview where ID = listboxID The code is at the link shown below update: The subform uses textboxes linked to the read-only record.
The main form can populate, no problem. However the SubForm data source is either populated OR is Null.
If it is Null, the subform stays blank. Two Objectives:
1. Set a boolean variable flag in the parent (main form) that subform record exist or that it is null. e.g. Parent obtain recordset count property from Sub form
2. Display the subform with blanks.
[URL]
I've received a database that is a digitized population register from the 19th century. All adults have been entered into the database, but all children are missing.
Every person has a unique number that corresponds with the original source (this variable is called 'no', this is not the autonumber primary key thing). Instead of searching in the original source which numbers are still missing, I would like to add the missing numbers (with no additional information, because I still need to type that in).
For example, the table now looks like this:
no - name_last - name_first - occupation etc
1 Smith Henry baker
2 Smith Mary
5 Williams John butcher
6 and so on
So 3 and 4 are missing.
How can I add these missing numbers automatically?