Dynamically Add Field To Record
Feb 24, 2005
This may be a really straightforward one but I cannot seem to find a solution.
I need to run a select query, (for a mailout) then mark the records that have been selected in the query on the main database table with a mailshot code.
Any ideas on how best to achieve this?
I was thinking of making the select query maketable (because I have to export a txt file of email addresses for a mailing list), then add a mailshot code field, then append this extra field to the main table.
The trouble is that I am not sure how to use the query to add this extra field to the maketable & once this has been done, how do I append the values to the original table when the mailshot code field does not exist, can it be dynamically created?
Any help will be much appreciated.
View Replies
ADVERTISEMENT
Apr 30, 2007
I need a way to dynamically store a particular value in "field_2" of the CURRENT record depending on whether or not the value of "field_1" of the CURRENT record is identical to the value of "field_1" of the PREVIOUS record within the same table. The table is sorted on "field_1".
So, if the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is also "ABC", then store a value of "PPP" in "field_2" of the current record. IF on the other hand, the value of "field_1" in the CURRENT record is "ABC" and the value of "field_1" in the PREVIOUS record is "XYZ", then store a value of "WWW" in "field_2" of the current record.
I have a report that will use these results to count only the number of records that have a "WWW" in "field_2".
Is this doable, maybe in a query somehow?
I should add that whatever the solution, it needs to be compatible with Access 2000.
View 1 Replies
View Related
Feb 18, 2006
Hi All,
I have built queries based on tables. The field names are the technical field names of an application. For example pernr is actually Personnel Number etc.
My queries are set up as follows:
Field = pernr
Table = etc
I would like to rename pernr to Personnel Number so that the column heading in the datasheet view is Personnel Number.
I know I can do this manually for each query by using the following as the field: Personnel Number: pernr
Is there any way of getting this be done dynamically using a lookup table etc. I have lots of queries and I don't want to manually change them all. Furthermore, if I wanted to rename Personnel Number to Personnel No. then I could do it in the mapping table rather than in all the queries.
Thanks,
Evan
View 2 Replies
View Related
Apr 27, 2013
Is it possible to assign a query to the recordsource of a form dynamically? I assume the answer is yes. Can this action be done when the form is not open? If yes what is the correct syntax to use when the code is in a normal module as a public sub? I tried many possibilities and could not get it to work.
Code:
Dim ForNm as string
Dim Qry as string
ForNm = "PersonalFm"
Forms(ForNm).Recordsource = Qry
Forms![PersonalFm].Recordsource = Qry
[Forms]![PersonalFm].Form.Recordsource = Qry
View 14 Replies
View Related
Sep 13, 2007
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
View 4 Replies
View Related
Dec 13, 2006
I'm fairly new to setting up Access databases. Been learning a lot. Ran into a snag that I'm hoping you can help me with.
I am using a form to display text labels and running statistics for our jail.
The form, "Complete_Worksheet" gets its data from a query, "qryComplete_Worksheet". Fields that make these up are:
ID (key)
Standard (text)
Outcome Measure (text)
Numerator/Denominator (text)
CalcValue (number)
ID, Standard, Outcome Measure, and Numerator/Denominator are queried from a table, "tblComplete_Worksheet". CalcValue comes from another query, "qryDataSelect". My problem is this:
In the final calculated statistic, some records are numerators and some are denominators. I need one more field, which I have been trying to create as a calculated control on my form which will divide each numerator CalcValue value by the denominator CalcValue value following it. Since these values change constantly as new data is entered into another data table, the calculated control needs to also change with them. I have been attempting to do this using the following expression:
=DLookUp("[CalcValue]","Complete_Worksheet", "[ID]="&"[ID]-1"/[ID]=")
I keep getting a #Error message in the control in form view. When I run the expression in the Immediate window, I get a Compile Error:
Expected: line number or label or statement or end of statement
Any suggestions on how to make this work?
View 9 Replies
View Related
Oct 1, 2005
Hi,
I have a subform called "Earthwork". I also have a query called earthwork200 and I need to dynamically transfer data from the earthwork to the query based on a certain column.
for example, the earthwork column i am using to transfer data to the query is "Desc" (the name of the column). How do i get data to dynamically goto the query as I click on a new item under the "desc" column?
Kindly HELP!!
View 1 Replies
View Related
May 23, 2006
Hi Guys
my database is essentially a questionnaire of around 200 questions. I'm trying to add a 'light' version to it.
I've added a 'include in light verion' tick-box to all the questions and a 'light analysis' tick box on the registration page.
The questions are all genertated via queries pulling out the appropriate ones as required. What I'm tying to do is add to these queries so that if the 'perfomr light analysis' tick box is ticked only those questions that have been indicated as light are included, and if it's not all teh questions are included.
I can do either-or but not both dynamically
Can anybody help?
Thanks
scube
View 1 Replies
View Related
Dec 6, 2006
I don't know if anyone can help me with this one at all.
I have a query that contains all the possible data that my users will wish to get data from but I want them to have the option to show/hide particular columns to make the query ad-hoc.
I have some code that copies my query to another of a unique name during the course of their Access session so they end up with a query with the name 'ABC' for example that they can run.
If this query ABC has columns A,B,C,D which are all defaulted to be shown and the user decides they are not interested in columns B and C is there Access code that is equivilent to this pseudo-code?
ABC.A.Show = True
ABC.B.Show = False
ABC.C.Show = False
ABC.D.Show = True
where ABC is a query, A..D are columns and Show is the checkbox in the query definition.
That I can run in VB 6.3?
Aenathras
View 3 Replies
View Related
Jun 1, 2005
Hello Guys,
I have proj name m1.mdb which has forms and reports.I have 2 forms form1 ,form2.In form2 I have button that creates new database named m2_date.mdb and creates report and form1 by using property DoCmd.TransferDatabase acExport, "Microsoft Access", sFilename, acForm, "form1", "form1", StructureOnly, StoreLogin,now this form1 has to be seen first when any user doubleclicks m2_user.mdb,so my question is, is there any way i can setup startup form properties dynamically ,without using startup form manually from tool properties of toolbar.
hope to get answer..
thanks
vsap
View 2 Replies
View Related
Dec 9, 2005
I have a form that has several rows of controls (comboboxes, textboxes, checkboxes, etc)
Each row is exactly the same. There is one row for every 'driver' in the 'SalesDrivers' Table.
Instead of manually adding a row when we add a new driver I would like to form to dynamically create the form based on the drivers in the table.
Any suggestions?
View 6 Replies
View Related
Mar 27, 2006
sorry if this q has already been asked, but i couldnt find what im looking for after searching..
i have a main form. there is a combo box that is that is bound to tblSupplierDetails. this combox displays the records stored in tblSupplierDetails fine. now beside this combobox i have a hyperlink that opens a subform called 'subfrmAddNewSuppliers'. this subform works correctly - ie. when details are entered here, they are stored in tblSupplierDetails
my prob is that if the user adds new supplier details via the subform, these details do not appear in the combo box for the user to select when they are returned to the main form.. they appear if the form is restarted
Ive inserted the statesment Me.ComboBoxName.Requery (with ComboBoxName ammended) into the event procedure for After Update for the combobox.
however, with this the new details which are added via the subform are replacing the previously added record - so theres only ever one record in tblSupplierDetails.. can someone pls help me with this?
View 7 Replies
View Related
May 8, 2006
I have a database that has a field [country] that stores the numerical id of the country from a table of countries. The student table also has a field called [mother tongue] and it seems to me that this is duplication.
I created a simple query based on a link table that tells me the country and its associated mother tongue.
SELECT [-LOOKUP-ALL-Country-LinkToMotherTongue].Country, [-LOOKUP-ALL-Country-LinkToMotherTongue].MotherTongueLinkNo, [-----LMotherTongue].Language
FROM [-----LMotherTongue] RIGHT JOIN [-LOOKUP-ALL-Country-LinkToMotherTongue] ON [-----LMotherTongue].[Reference no] = [-LOOKUP-ALL-Country-LinkToMotherTongue].MotherTongueLinkNo;
It seems to me to be an overhead to store in the student table the mother tongue id.
How can I create a text lable that dynamically shows the mothertongue as i move over records?
that way the user can see the students mother tongue but I only store the country table and link table to mother tongue. as opposed to a mother tongue entry against all students.
regards in advance
Peter
View 2 Replies
View Related
May 9, 2006
Hello Access heroes..
I'm using the following code to dynamically link to images in a popup form:
thisPicString = GetPath(CurrentDb.Name) & DLookup("details", "infoTbl", "Item = " & Chr(34) & "Image Folder" & Chr(34)) & ""
MsgBox thisPicString
Me.Image1.Picture = thisPicString & "DNA.gif"
Me.Image17.Picture = thisPicString & "wDNAslogo1.gif"
Me.Image18.Picture = thisPicString & "defralogo1.gif"
The thing is, it finds the images fine, but before it does this I get an error saying Access cannot open the file name xyz.
xyz is the path the picture has originally, but doesn't exist anymore.
Any ideas? I've tried this code at _Open, _Load and _Current, but all to no avail. At what point is access trying to link to these images? Can I get the code in there first?
Cheers,
View 3 Replies
View Related
Oct 31, 2004
How do I let users choose the way they want to sort their reports through a form dynamically?
View 1 Replies
View Related
Jan 26, 2006
Hi, I am new to asp development and have been searching all through the web and books to help me but I am a little stuck and would appreciate some help please with updating a second combo box.
I am developing an online fault logging system for a school where the teachers/pupils can report a fault with any computer equipment to the technicians so they then can fix it.
I have two combo boxes called ResourceType and Resource ID which are created and populated from two different tables in an Access database (I dont want to annoy you with the code but if you like I can show you)
When ResourceType combo is selected it should automatically update the ResourceID combo from a table in the database. eg. ResourceType=Laptop then ResourceID should be populated with all the entries in the DB which correspond to Laptops, i.e. Laptop001, Laptop002 etc.
ResourceType combo is populated from table tblResourceType in the Db and ResourceID is populated from table tblResourceID. This works fine when the page is first loaded but I have problems with the onChange="handleChange(this)" function for the ResourceType combo. It is :
Code:function handleChange(nSelection){var IDSelect,iCountIDSelect = document.form.ResourceID //Reset the ResourceID combo contentsfor(i=IDSelect.length; i>0;i--){ IDSelect.options[i-1] = null} //Open Database<%set db=server.createobject("adodb.connection")db.Open"DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("MCB Fault Log.mdb")dim dbaseset dbase=server.createobject("adodb.recordset")%> <%Dim iCount 'counter variablesSQl = "SELECT ResourceID from tblResource"dBase.Open sSQl , db%> //Reset Counter iCount = 0<%do while not dBase.EOF%> IDSelect.options[iCount] = new Option(<%=dBase("ResourceID")%>);//********This does not work****** //IDSelect.options[iCount] = new Option(iCount);//***HERE**This works (places the count of each record in the combobox iCount = iCount + 1; <%dBase.MoveNextloop%>}
Dont worry about the SQL statement for now, I am happy just for any table to fill into the combo. Look at the //********This does not work****** line - I think I have the syntax wrong, I have looked at lots of examples on the net and I cannot find one similar to this. The line ***HERE** places the number of the count into the combo box so at least i know the the database is being accessed.
Does anyone know what is going wrong here.
Thanks in advance for any help you may give
Cush
View 1 Replies
View Related
Oct 27, 2006
Hi,
Does anyone know how to add controls to a form, dynamically??
View 6 Replies
View Related
Mar 16, 2014
got the following issue:
There are two tables A und B.
Table B contains columns like 1FC, 2FC,...., 12 FC
standing for forecast sale quantities.
Now I want the user to decide how many FC columns he wants, always starting with 1FC.Let's say he wants three FC columns.Now I want to map these 3 FC columns to the table A. Both tables contain column Product_Classes.Can I shrink table B to the 3 FC columns ?
View 3 Replies
View Related
Feb 6, 2014
I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?
Can this be done? because if i use find it will only search the filtered form i have onload of the form?
My onload event is based on fosusername()
View 3 Replies
View Related
Jan 15, 2014
After I enter data into one field in a record I would like the form to save the record when I move to another field in the same record. It seems that the record is only saved when I exit it entirely. Is there a way to save a record when moving between fields in that record? Can this be done without using an Event Procedure for each field?
View 8 Replies
View Related
Apr 10, 2006
Hi,
Does anyone know of a way to dynamically fill the content of a form at runtime. I want, for example, when a user clicks a button, a textbox is dynamically added (but I would prefer not to use hidden objects). I've tried the "CreateControl" option but I can't seem to get this to work. Any coding examples would be much appreciated.
Thanks,
Mark.
View 4 Replies
View Related
Jun 1, 2005
I had posted this in another area but didn't receive a response and my whole project has ground to a halt until I sort this out :(
I have made a database with front and back ends. Now I wish to be able to use the front end over the Internet but the IP address of the back end keeps changing weekly because of my ISP.
Now I have made a splash screen at the start of the program which asks for the IP address and when this is entered the front end links to the back end - but I can't get the code right for this
The code I have at the moment is embarrasingly wrong so I'm not putting it up here
The code I have at the moment has a box that asks for user to enter the IP address - I need it to use the IP address to enter the database at ip//data.mdb
Regards, James
View 3 Replies
View Related
Feb 5, 2008
Hi,
I am trying to create a series of MS access tables from within a SQL 2000 DTS package. The names of the tables reflect the date range of the data within them. Creating the "Create table ddmmyy ...." statement isn't a problem (full code below), but doesn't run as it creates a Jet error - "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Does anyone know if what I'm trying to do is possible.
Before anyone suggests it, yes I have posted this on SQL server forums.
declare @TheName char (8)
,@Cmd char (500)
select @TheName = (select max(LastRunDate) from NSP_Analysis_Dates)
select @Cmd = 'CREATE TABLE `NSP_Analysis_' + @TheName + '` (`TYPE` VarChar (200) NULL, `VENDOR_NO` VarChar (10) NULL, `VENDOR_NAME` VarChar (50) NULL, `AMOUNT_CLAIMED` Currency NULL, `REGION` VarChar (100) NULL, `CLUSTER` VarChar (100) NULL, `PLANT_CODE` VarChar (15) NULL, `PLANT_NAME` VarChar (50) NULL, `DATE_OF_SERVICE` DateTime NULL, `STATUS` VarChar (200) NULL, `TICKET_NO` VarChar (20) NULL, `NOTES` VarChar (255) NULL, `DATE_ENTERED` DateTime NULL, `AuthorisedByName` VarChar (50) NULL)'
sp_executesql @Cmd
View 1 Replies
View Related
Mar 5, 2007
Hi
I have 120 tables, each with the same name except 2 identifying characters at the end eg pc_dist_ab, pc_dist_al
Each table currentnly has 3 columns.
I would like to be able to add 2 additonal columns to each table with one query and was hoping an alter table query where the table name matches pattern would have worked but evidently not.
Is there a way to build some sort of dynamic query to add extra columns to these different tables at the same time?
To save another post I guess Once this is done I would then like to create 1 main table by creating a new table and appending all the files together- again I would prefer to be able to run this once.
I'd appreciate any help/thoughts as to whether this can be done?:confused:
Thanks
View 1 Replies
View Related
May 11, 2005
I have a form, very basic.. it contains more or less an inventory frontend in which users input computer equipment that we carry in our state dept.
To make this very brief, in my form at the end I have an "Additonal Information" textbox in which users can add additional notes, comments, etc.
As textboxes are constrained to 255 characters, I chose to show an additional textbox directly below the Additional Information textbox, with a control source set to =Len([Additional_Information]) to show users how many characters they have typed.
What I'd like to do though, is make this value dynamically update while the Additional Information textbox is still in focus... so far, all I can get it to do is update once a user has tabbed away from the additional information textbox.
Is there a way to make this update in real-time?
I appreciate any feedback at all.. thanks :)
View 2 Replies
View Related
Sep 25, 2005
Hi All,
I am new to Access and looking for some help.
I have a query, which reads the combo box value and loads the result in Text box. It works fine when I load the Form.
Now here is the problem… If I select another values from combo box the query result remains the same (in text box). How do I make it work dynamically?
So when I select new value in combo box, the resulting query gets updated in textbox.
Thanks
View 3 Replies
View Related
