i cannot see new field in form list Results


Page 1 of 3.
Results 1...20 of 53

Sponsored Links:



I have added a field to a table used by a form. Whatever I do, the field list still shows the previous fields. Help suggests there is a refresh button but it is not visible anywhere. I've tried logging out and back. I notice that the SELECT for the form also does not include the new field.

The form does not use a query. I hope!.




Im sure this is simple, but I cannot see how to display a list of function rooms available at a given hotel.

I have a hotels database(table) where a single record contains the usual address/telephone/email... details about each hotel;

I would like to have a combo box on my form where I can select previously added room names or type new rooms names (which will get added to the existing list) at that hotel and have this information saved to a single field in that hotels record.

Is this possible?




I am totally new to Access and, through purchasing the "Step by Step Microsoft Office Access 2007" book I understand the basic concepts. One section in the book illustrating how to simplify a process shows how the key field can be auto populated with the 1st 3 letters from the last name and the first 2 letters from the first name after you update the last name field. (Chapter 5, pg 124 for those who have this book).

After following the instructions for the practice files, I was never able to get this to function according to the VBA code which I was instructed to use:

'Create variables to hold first and last name
' and customer ID
Dim fName As String
Dim lName As String
Dim cID As String

'Assign the text in the LastName text box to
' the lName variable.
lName = Forms!customers!LastName.Text

'You must set the focus to a text box before
' you can read its contents.
Forms!customers!FirstName.SetFocus
fName = Forms!customers!FirstName.Text

'Combine portions of the last and first names
' to create the customer ID.
cID = UCase(Left(lName, 3) & Left(fName, 2))

'Don't store the ID unless it is 5 characters long.
' (This would indicate both names not filled it.)
If Len(cID) = 5 Then
Forms!customers!CustomerID.SetFocus

'Don't change the ID if it has already been
' entered...perhaps it was changed manually.
If Forms!customers!CustomerID.Text = "" Then
Forms!customers!CustomerID = cID
End If
End If

'Set the focus where it would have gone naturally.
Forms!customers!Address.SetFocus

Now, my tables/forms are named differently and, bringing my web development and sparse PHP programming knowledge to bear, I can understand that I first need to change the names of the forms listed here (customers) to my form name (Clients) and the field name of CustomerID to my field name of ClientID. I see that after updating the last name field, the cursor is supposed to automatically migrate to the address field. I can also see where, since I want to add the 1st 4 letters from the last name and the 1st 2 from the firstname which makes this key field a 6 digit field, where I would need to do this.

I have tried to get this to work by starting a new record in form view but nothing happens. I do not know much about VBA. Can anyone help me with this problem? I cannot use the autonumber function as this is a database for a law firm and I need the ClientID field to populate as I have explained above.




I have a table of valid addresses (table1). An address can have have several formats e.g. street number, name, building name, floor etc. Because of the various address formats, the key to the table is a composite of all of the individual parts of the address (this gets over the problem of having nulls in key fields).

A second table (table2) is used to hold details of various sites within our organisation. This table holds the address of each site which must be a valid address as defined in table1. Table2 has a foreign key link to the composite key in table1.

Form1 is used to enter new sites. The user enters the various address components of the site address on this form.

My idea was to populate a text control with the address components as the user types these in (I can do this bit). What I need to know is how I get the value of the text control to the table2 key field (composite address) so that the address values entered can be validated against the addresses in table1. After a bit of searching, I found a post that suggested a "setvalue" macro which would set the value of the table2 key field prior to update. The setvalue action does not appear to be available in the macro action list.

Can you tell me why I cannot see the setvalue action?

Do you have another solution that I might apply?




Hi,

I have a form with a subform. On the subform I have a combo. In the not in list of that combo I trigger a second form as popup dialog to enter a new combo option, along with some linked fields.

When the user clicks the 'close' command button it closes that form and returns them to the original form which is still open.

I woudl like th combo to be requeried so the new item added now appears without having to close and reload the form.

I already have this in operation on other forms, although in a slightly different setup. I use this code:


	Code:
	Dim frm As Form 'This code requeries and refreshes all open forms in the current project
For Each frm In Application.Forms
If IsLoaded(frm.Name) = True Then
' requery and then refresh the form
frm.Requery
frm.Refresh
End If
Next frm


That doesn't work for some reason so I have tried lots of different combination and locations for the code yet none seem to be able to requery the subform on the main form while open and I cannot see why.


My most recent attempt is this different code on the close button:


	Code:
	Private Sub cmdClose_Click()
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord

Me.Requery
Me.Refresh


DoCmd.Close

Form![frmHazards_Subform].Requery


End Sub


Can anyone help please?




Hi

WinXpPro Sp2
Access2002/2007

Does anyone know how to achieve this please? I've spent 3 x 12/14 hour days trying to get it to work. I really don't think what I'm trying to achieve is so spectacular?

Simple Form with a Subform. The idea is to permit users to deselect and/or re-order the order of parameters used in things like orderbys or fields on printed reports. See attached screen shot for a flavour.

Because I have some checking to do, I use SQL Transactions so that the users' changes don't get commited until the appropriate time (if at all).

I tried with DAO as my subform recordset but I need a Sort capability so that it gets displayed correctly...so I moved to ADODB for the subform's recordset.

Whenever I try to edit a field and .Update it I get errors... usually this one.

Quote: Error No: -2147217864 - Error Decription: Row cannot be located for updating. Some values may have been changed since it was last read. I make these declarations in the main form module.

	Code:
	Public Edit_rstADO As New ADODB.Recordset
Public Edit_Cnn As ADODB.Connection
Public Edit_Transaction_Begun As Boolean

In the Form-Open I build the transaction resources

	Code:
	 'Set up Transaction resources
Set Edit_Cnn = CurrentProject.AccessConnection
 
'Set SQL String to Load an empty recordset 
SQLLine = "SELECT TBL1.* FROM Form_Sort_And_Filter_Fields AS TBL1 WHERE Unique_No = -999" 
 
'Open the RS
Edit_rstADO.Open SQLLine, Edit_Cnn, adUseClient, adLockOptimistic
Edit_Cnn.BeginTrans
Edit_Transaction_Begun = True

I then manually add some records into the rs - just 1 shown here

	Code:
	'Add some records into the RS
Edit_rstADO.AddNew
Edit_rstADO![Table_Friendly_Name] = Sort_Args(1)
Edit_rstADO![Field_Friendly_Name] = Sort_Args(2)
Edit_rstADO![OrderBy_Direction] = Sort_Args(3)
Edit_rstADO![Field_Selected] = True
Edit_rstADO![List_Order] = List_Order_No
Edit_rstADO.Update

'Set the sort order on the RS and point subForm at it

	Code:
	Edit_rstADO.Sort = "List_Order"
Set My_Fields_SubForm.Form.Recordset = Edit_rstADO

So far so good!

I have move-up & move-down buttons which do the following:-

Move-Up button
a) Grabs the Unique_No (PKey) and existing List_Order_No of current record.
b) Substracts 1 from the current row's List_Order_No
c) Does a FIND to see which record 'owns' that list order no. moves to that record and does an update of List_Order_No to demote it.
d) Does a FIND to go to previously selected record and does an update of the List_Order_No to promote it.

Up-Button-Click

	Code:
	'Dim all the variables here.........including another Rs:-
Dim....
Dim....
Dim rst As New ADODB.Recordset
 
Set Cntrlbx = My_Fields_SubForm
 
Extg_List_Order = Cntrlbx.Form!List_Order
Trgt_List_Order = (Extg_List_Order) -1
 
Set rst = Cntrlbx.Form.RecordsetClone
rst.Find "List_Order=" & Trgt_List_Order
If rst.EOF Then
       MsgBox "Ouch"
End If
 
'Move the Form Rs to the record just found
Cntrlbx.Form.Recordset.Bookmark = rst.Bookmark
Cntrlbx.Form.Recordset.Fields("List_Order") = Extg_List_Order
Cntrlbx.Form.Recordset.Update
 
etc etc....

And it's at the .Update that it fails.... If I leave thr .Update out it fails when trying to move to another record.

I've tried different cursors, connections, AddNews before and after assignment of the subform Rs... I saw threads talking about having no defaults set up for nunber fields.... well, you name it, I've tried it...

Thanks




Hi,

I apologise for all I am asking, but I am a little rusty (about 12 years since I last used Access), and I am slowly getting back to grips! Things were a lot different back then!

I also apologise for any confusion as I have Aspergers, and sometimes say things in my head instead of communicating what I wanted to say!

Iam trying to develop an "Internal Email" form to email [Employees] using Gmail via a module.

So far I have made a table of emaployees and picked a macro from this forum.

There are lots of things I need to know how to do, but mainly I need a search utility for multiple fields from records in a table that will allow the user to place a different field associated to the record found/selected by the user
ie: search [firstname] & [lastname] fields, and upon selecting required record from query results, output [email] field to a textbox (may likely need results that can be used as multiple selections with comma seperated outputs upon selection)

My overall goal here is to create a textbox in the form that will look up the [FirstName] and [SecondName] fields at the same time, and (if not too much work for you!) show the results as you type as a checklist.

in other words I want the results to display as a tickbox selection list with the results like so:
Table field data:
[FirstName] & " " & [Lastname] "(ID: " & [EmployeeID]
Text Output:
□ John Smith (ID:1)
□ John Doe (ID:2)

When this list comes up, I would like to be able to select multiple names that when selected, will have each record's respective Email address [Email] added into a "To" textbox, ready to be read by the email module for when the email is sent.

I am also having trouble with the button I want to use to send the email, I cannot seem to figure out how to get the macro I am using to identify the [To], [Subject] & [Body] details.

Here is my module (as you can see I have tinkered with it a little, and this was working when I first made my form in the simplest setting):

Public Function send_email()


Set cdomsg = CreateObject("CDO.message")
With cdomsg.Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "benjamin.goddard@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "forgetm3n0t"
.Update
End With
' build email parts
With cdomsg
.To = EmployeeEmail.EmailTo, "benjamin.goddard@gmail.com"
.From = "ben@leeli.co.uk"
.Subject = EmployeeEmail.EmailSubject 'was with "subject here"
.TextBody = EmployeeEmail.EmailBody 'was "the full message body goes here. you may want to create a variable to hold the text"
.Send
End With
Set cdomsg = Nothing
End Function




You might also notice I set the Email button to save a record of the Email, as eventually I will be making a complete email suite with an inbox and outbox etc

Anyway, I have made a new Database and imported the stuff that you might need to look at to help!

Thanks in advance peepz!

Ben Attached Files Email.mdb (288.0 KB, 4 views) Reply With Quote 03-28-2012, 01:08 PM #2 pbaldy Who is John Galt? Windows XP Access 2007 Join Date Feb 2010 Location Nevada, USA Posts 9,234 Personally I would probably have the textbox change the source of a listbox, and let the user select from the listbox. Your reference to the form is off:

Forms Refer to Form and Subform properties and controls




I added a query to the form that pops up with you go in to create a new referral to filter to a blank form. Now if you click on the "Add Accommodation Information" button the pop up screen is blank. There should be data entry fields. Can anyone take a look at this and tell me how to fix this? Attached Files Final CM Referral Database 2012.zip (157.7 KB, 6 views) Reply With Quote 07-16-2012, 08:50 PM #2 Missinglinq Expert Windows 7 64bit Access 2007 Join Date May 2012 Location Richmond (Virginia, not North Yorkshire!) Posts 828 Here's my standard response to this question:

Controls don't appear in Form View when three conditions exist at the same time:

The Form is Bound to a Table or QueryThere are No Records in the underlying RecordsetThe Form cannot have New Records added
The reasons that a Bound Form cannot have New Records added include:

AllowAdditions for the Form is set to NoThe underlying Query the Form is based on is Read OnlyUser doesn't have Read and/or Write Permission for the Folder where the Data resides.Folder the File resides in (in versions 2007/2010) not having been declared as 'Trusted'Form's Recordset Type is set to SnapshotAll Controls on the Form being Locked or Locked and Disabled.
To see if #2 is the case, from the Objects Dialog Box go to Queries and click on the Query that your Form is based on to open it. Now try to enter a New Record directly into the Query. If you're unable to do so, this is the cause of your problem.

If your Query is Read Only, follow this link to Allen Browne’s article explaining the reasons this happens.

http://allenbrowne.com/ser-61.html

Since you kindly provided a copy of your app (thank you for that) it can be seen that your problem relates to #2, in the first list, and # 1 and #2, in the second list above.

The Form in question is based on a Multi-Table Query that has no Records The Form's AllowAdditons Property is set to No.The Query is Read-Only.
Unfortunately, I don't have time to get into analyzing your app, which appears quite complex, in depth, but the above should point you in the right direction. If you run into problems, post back and myself or others will be glad to assist you.

Good luck with this!

Linq ;0)>




Hi guys I am working on a CRM db, and I stuck on the forms of it.


Here is the issue, while receiving a call from a customer db user might need to log multiple issues that are not related to one another, and more importantly these issues might have unique variables for example:

During a call a customer complains about the service he is receiving BUT during the same call customer requests to receive an estimate for a different service
-In a complain our variables would be service date, complain type, etc.
-In an estimate our variables would be job size, service type, frequency of service etc.

As you can see I cannot treat complains and estimate requests as new records in the same table because their fields will not match.
They way I am hoping to solve this problem is by having a subforms show up in a list like records in table embedded in a form.
I guess this would require a button similar to new record button but instead when clicked a new subform appears on the screen, hopefully in the same window.

I should not that I don't know how to code in VB SQL or Macros but I know enough about them to embed there wherever needed.




I'm either having a brain cramp or something isn't as obvious as it seems.

Scenario: I have a DB (Ac 2k3) that will do some departmental workload tracking across a large number of machines requiring work. I'm rolling my own security inside the forms and hiding the DB window. Each user in my list of authorized users will have one of four roles, each with increasing access rights vis-a-vis the previous one.

The DB has a startup form that is a switchboard. It is supposed to stay open for the lifetime of the session, and you close the session with a button on the form. The startup form is responsible for defining the security. I have some auditing defined for this process, too. The switchboard logs things like opening and closing forms, but each individual form is responsible for at least some parts of its own security.

I'm in a domain-based environment with strict Windows security, so there is a user login name available from domain-based information. In the startup form, I use the Environment("Username") construct to look up usernames. I get the one I want and that lookup works OK. It is the correct username for the domain, and that username is of course unique across the domain.

I do a quick recordset operation to select the Username index and find that username (or classify the user as a guest if no record). If the user is known, I look up the user's role field. This will be a simple number that defines just how much of the system each user can use - disabled, guest, staff, DBA. Don't need more complex than that.

OK, so the theory is that when I open a form from the switchboard, the switchboard is still open, just "not on top." Any public variables from the switchboard should still be instantiated. But I can't find them using any syntax and logic available to me.

More specifically, the Form_OnLoad routine in the second form cannot see the public variables in the startup form, variables which it would use to decide what this user can do. So I researched the various ways to do this and I'm coming up blanker than I should. I'm obviously missing something, but it isn't clear as to what.

In this situation, I have two forms open, A (the switchboard) and B (the form opened via switchboard).

In the class module for A, I have a couple of public variables, call them SAPK (a person tracking number) and AccRights (the role number), both LONG.

First blush, VBA code in B in the the _OnLoad event should be able to see both of these because A is still open. But no, it cannot.

Second blush, searching the forum, I found what DCrake says will work, where you create a public function to get the public variable. Define the public functions in the class module of A. Can't see the functions when I do that.

Third blush, moved the variables to a general module. Had Form A load the variables. Stepping through the debugger, the values are defined. Still cannot see the public variables from VBA in form B.

Fourth blush, used the GetSAPK and GetAccRights public functions from the general module instead of trying direct access. Still no joy.

I thought about creating a class object, then populating it with the info with property let and property get stuff, but that still leaves me with the question of finding the class object from VBA in class module of B if I created the new object in the class module of A (or, apparently, the general module). I'm in the same boat except now it is finding the class object instead of finding the variables in the class module.

The symptom seems to be that I cannot find the item I seek, whether it is the SAPK value or the AccRights value. No combination of syntax seems to work. No, there are no name overlaps in the modules.

If I use syntax based on Form_OpeningDB, it seems to know the names of the values, but when I run the VBA code, it can't resolve the names even though the form in question is open. I've tried variants of Forms!OpeningDB and (using the collection syntax) Forms("OpeningDB") - but nothing seems to fly here.

I downloaded and examined DCrake's example, and I'm damned if I see what I'm doing different. All I want is to see that furshlugginer LONG variable defined in form A from form B. So, any thoughts?




Ok guys - I cannot quick grasp this

I need to hard code the directoy to absolute to
C:Bumblebee

(I will switch the code to my network..)

but I cannot see where





Option Compare Database
Option Explicit
'Const TextMerge As String = "merge.txt"
' May 20/2003 - see below comments why txt file name was changed.

Const TextMerge As String = "merge.888"


'******************************************
'* Word merge code *
'* (c) 2001 Albert D. Kallal *
'* kallal@msn.com *
'* *
'******************************************
'
' Usage:
' In code on a form with a data source, just place the following
' command behind a button
'
' MergeSingleWord
'
' That is it!!!
'
' You can also specify a dir for the above. This dir location is relative
' to location of the access dir. The default dir is "Word". Hence, the real
' usage is:
' MergeSingleWord [dir],[bolFullPath]
'
' Example:
' MergeSingleWord "Customers"
'
' The above would use/create the templates in a dir called Customers (relative to app dir).
' The "" is optional. If you leave out the "", then my code appends a "" to the dir
'
' An absolute location can be specified as:
' MergeSingleWord "c:MyWord", True
' The use of the True above forces the dir to be a full path name, and not relative
' to the app dir. The path must be a FULL path, and not relative.
'
'==========================================
' Revisions
' Date who Comments
' May 20, 2003 ADK - added on error resume next to the mkdir command in GetWordDir
' May 20, 2003 ADK - changed merge.txt file to merge.888 to fix text import bug
' (this is a know problem when you turn off file extensions
' in windows, the mail merge will fail KB article 137385)
' Aug 06, 2003 ADK - added ability to use different dire for each form.
' Aug 09, 2003 ADK - added a listbox sort routine
' Aug 28, 2003 ADK - change the "modify" template option to *always* set the
' data source to merge.888. That way, just hitting modify option
' will set the data source to the correct dir.
'
' Oct 22, 2003 ADK - changed the "qu" routine that surrounds the text data field to
' remove all " quotes to a ' (single quote).
'
' Oct 29, 2003 ADK - removed use of AppActivate "Microsoft Word" to use
' wordApp.Activate (this works with all versions, including xp where
' the window names are "separate"
' Feb 14, 2004 ADK - Added the code to set the data source EACH TIME a word doc is loaded.
' (This was done to fix problems with office 2003, and the message:
' "Opening This Will Run the Following SQL Command Message"
' There is a number of registry settings that can be changed. However
' just setting the data source EACH TIME the word doc is loaded also
' seems to by-pass the run SQL command nag message. Since this is hole
' in what is supposed to prevent automation code from setting the data
' source, I am betting that future releases will beak my code!
' Sept 24, 2004 ADK - added some code to allow name of output doc to be set.
' Mar 06, 2005 ADK - unbound text boxes are now included for single word merge
' Sep 07, 2005 ADK - mergenoprompts now allows sql string to be based
' Oct 10, 2006 ADK - text boxes on forms now can be used in the merge.
' Oct 14, 2006 ADK - fill prompt fields cased menu bars to not show, fixed by
' moving the visible + activate code to BEFORE the merge.
' Dec 11, 2006 ADK - fixed a focus bug, and now use a SEPERATE instance of
' the "open" word document to fix a problem when docuemnts
' are already open

Public Function MergeSingleWord(Optional strDir As String = "Word", _
Optional bolFullPath As Boolean = False, _
Optional strOutPutDoc As String = "")

' Main Word merge function
' Albert D. Kallal
' kallal@msn.com
'
'
' starts the whole process of a "merge" template (single record) in rides.
'
' Simply place this command behind a button on a form.
' A function was used here in place of a "sub". This was done since a
' custom menu bar can call this code by placing =MergeSingleWord() in the
' menu's on-action. Thus, if you use custom menu bars, this code will work!
' This code thus picks up the active screen name, and functions from that.

' Parms are:
' strDir optional dir (include the ) the dir name - relative to applicaton dir
' bolfullPath optinal flag. Set to TRUE if the above dir is a full path name. If you
' do NOT set this flag (or leave it as false), the the path name is relative
' to the appliction dir.
' stroutPutDoc Name of the document to be saved to disk. (full path requied here)

Dim strOutFile As String ' temp csv merge text file name
Dim frmF As Form
Dim strDirPath As String ' full path name to working dir

Set frmF = Screen.ActiveForm
frmF.Refresh

strDirPath = DirToPath(strDir, bolFullPath)

strOutFile = strDirPath & TextMerge

' output our simple merge file

If MakeMergeText(frmF, strOutFile) Then
DoCmd.OpenForm "GuiWordTemplate", , , , , , strDirPath & "~" & strOutPutDoc
End If

End Function


Public Function MergeAllWord(strSql As String, _
Optional strDir As String = "Word", _
Optional bolFullPath As Boolean = False, _
Optional strOutPutDoc As String) As Boolean

' Merge all reocrds from the form.
' This rouinte can take any sql statement you pass, and create
' a merge doc. Thus, this is used for "many" merged, and not
' a single merge.

' Simply this rouintes writes out a csv file based on the
' sql, and then launches the merge form.

' Parms are:
' strDir relative path to dir
' bolFullPath if set true, then above path is NOT relative
' strOutPutDoc if set, then write out the merged doc to this file

' build our merge file, and write a "csv" file to disk

Dim strDirPath As String ' full path name to working dir
Dim OneField As DAO.Field ' dao code
Dim strFields As String
Dim strData As String
Dim intFile As Integer

Dim rstOutput As DAO.Recordset
Dim strOutFile As String ' csv file output name

On Error GoTo exit2 ' if sql is bad...simply exit...

Set rstOutput = CurrentDb.OpenRecordset(strSql)

If rstOutput.RecordCount = 0 Then
' no records...exit.
GoTo exit1
End If

' build the merge file, but show the process bar
'
clsRidesPBar.ShowProgress
clsRidesPBar.TextMsg = "Building merge file..."

On Error GoTo exit1 ' if sql is bad...simply exit...
rstOutput.MoveLast
rstOutput.MoveFirst

' set max value of progress bar to number of records
clsRidesPBar.Pmax = rstOutput.RecordCount

' build the first line of fields for csv

For Each OneField In rstOutput.Fields
If strFields "" Then strFields = strFields & ","
strFields = strFields & qu(OneField.Name)
Next OneField

' build the merge.txt file

strDirPath = DirToPath(strDir, bolFullPath)
strOutFile = strDirPath & TextMerge

'delete the out file if there
On Error Resume Next
Kill strOutFile

' now open file...

On Error GoTo exit1

intFile = FreeFile()
Open strOutFile For Output As intFile
Print #intFile, strFields

' output all data
Do While rstOutput.EOF = False

strData = "" ' one line of data for csv file
For Each OneField In rstOutput.Fields

If strData "" Then strData = strData & ","
strData = strData & qu(rstOutput(OneField.Name))

Next OneField

Dim vField As Control




Print #intFile, strData

rstOutput.MoveNext
clsRidesPBar.IncOne
Loop

Close intFile

MergeAllWord = True

clsRidesPBar.HideProgress

DoCmd.OpenForm "GuiWordTemplate", , , , , , strDirPath & "~" & strOutPutDoc

Exit Function

exit1:

clsRidesPBar.HideProgress

exit2:

MsgBox "No data was created for this merge" & vbCrLf & _
"Make sure the sql is correct" & vbCrLf & _
"sql was " & vbCrLf & vbCrLf & strSql, _
vbCritical, "no data for this merge"

MergeAllWord = False


End Function

Public Function MergeNoPrompts(strDoc As String, _
Optional strDir As String = "word", _
Optional bolFullPath As Boolean = False, _
Optional strOutDocName As String, _
Optional strSql As String = "", _
Optional bolPrint As Boolean = False, _
Optional StrPrinter As String = "")

Dim frmF As Form
Dim strFullDocName As String
Dim strDirPath As String
Dim strOutFile As String

Set frmF = Screen.ActiveForm
frmF.Refresh

strDirPath = DirToPath(strDir, bolFullPath)

strFullDocName = strDirPath & strDoc
strOutFile = strDirPath & TextMerge ' temp text file name

' sql passed?
If strSql "" Then
If MakeMergeAll(strSql, strDir, bolFullPath) = False Then
' could not create...exit
Exit Function
End If
Else
If MakeMergeText(frmF, strOutFile) = False Then
' could not create...exit
Exit Function
End If
End If

Call RidesMergeWord(strFullDocName, strDirPath, strOutDocName, bolPrint, StrPrinter)


End Function

Function GetAppDir() As String

' This routine simply returns the current applction dir
'+ word
' If the dir does not exist, then we create it.
' This is the dir where the word docs will be created.

Dim strDB As String

' build a string based on the CURRENT mdb direcotry + word

strDB = CurrentDb.Name

GetAppDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))

End Function

Function DirToPath(strDir As String, bolFullPath) As String

If Right(strDir, 1) "" Then
strDir = strDir & ""
End If

If bolFullPath = True Then
' full path name to a dir ...not relative
DirToPath = strDir
Else
DirToPath = GetAppDir & strDir
End If

Call CheckDir(DirToPath) ' checks if dir exist..if not it creates the dir

End Function
Sub CheckDir(strDir As String)

If strDir = "" Then Exit Sub

If Len(Dir(strDir)) = 0 Then
On Error Resume Next
MkDir strDir
End If

End Sub

Function qu(vText As Variant) As String
' takes a string and surrounds it with double quotes
' All " (double quotes) are converted to ' (single quotes) before
' this is done

If IsNull(vText) = False Then
If InStr(vText, Chr(34)) > 0 Then
vText = strDReplace(CStr(vText), Chr(34), "'")
End If
End If

qu = Chr$(34) & vText & Chr$(34)

End Function


Function strDReplace(vText As String, strSearchFor As String, strReplaceTo As String) As String

Dim intFoundPos As Integer
Dim intSearchLen As Integer
Dim intReplaceLen As Integer

intSearchLen = Len(strSearchFor)
intReplaceLen = Len(strReplaceTo)

intFoundPos = InStr(vText, strSearchFor)

Do While intFoundPos > 0
vText = Left$(vText, intFoundPos - 1) & strReplaceTo & Mid(vText, intFoundPos + intSearchLen)
intFoundPos = InStr(vText, strSearchFor)
Loop

strDReplace = vText

End Function
Function RidesMergeWord(strDocName As String, _
strDataDir As String, _
Optional strOutDocName As String, _
Optional bolPrint As Boolean = False, _
Optional StrPrinter As String)

' This code takes a word document that has been setup as a MERGE document.
' This merge document is opened, then mailmerge is executed. The original
' document is then closed. The result is a raw word document with no connectons
' to the merge.txt (a csv source data file).

'Parms:
' strDocName - full path name of word doc (.doc)
' strDataDir - dir (full path) where docuemnts and the merge.888 file is placed
' strOutDocName - full path name of merged document (saved).
' bolPrint - if true, then output docuemnt is printed - if strOutDocName is suppled then we close the docuemnt
' strPrinter - sends output to the printer name
'
'
' The above parms are suppled by other routines. You likey should not need to call this
' routine directly. See the sub called MergeNoPrompts.

' Albert D. Kallal (c) 2001
' kalla@msn.com
'
Dim WordApp As Object ' running instance of word
Dim WordDoc As Object ' one instance of a word doc
Dim WordDocM As Object ' one instance of a word doc
Dim strActiveDoc As String ' doc name (no path)
Dim lngWordDest As Long ' const for dest, 0 = new doc, 1 = printer
Dim MyPbar As New clsRidesPBar ' create a instance of our Progress bar.


MyPbar.ShowProgress
MyPbar.TextMsg = "Launching Word...please wait..."
MyPbar.Pmax = 4 ' 4 steps to inc
MyPbar.IncOne ' step 1....start!

On Error GoTo CreateWordApp
Set WordApp = GetObject(, "Word.Application")
On Error Resume Next

MyPbar.IncOne ' step 2, word is loaded.

Set WordDoc = WordApp.Documents.Open(strDocName)

MyPbar.IncOne ' step 3, doc is loaded

strActiveDoc = WordApp.ActiveDocument.Name
'wordApp.Activate

If bolPrint = False Then
WordApp.Visible = True
WordApp.Activate
WordApp.WindowState = 0 'wdWindowStateRestore
End If

WordDoc.MailMerge.OpenDataSource _
Name:=strDataDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0, _
Connection:="", SQLStatement:="", SQLStatement1:=""


With WordDoc.MailMerge
.Destination = 0 ' 0 = new doc
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .datasource
.FirstRecord = 1
' .LastRecord = 1
End With
.Execute Pause:=False
End With
Set WordDocM = WordApp.ActiveDocument

MyPbar.IncOne ' step 4, doc is merged
WordDoc.Close (False)

WordApp.Visible = True

If strOutDocName "" Then
'wordApp.ActiveDocument.SaveAs strOutDocName
WordDocM.SaveAs strOutDocName

End If

If bolPrint = False Then

WordDocM.Activate

Else

' print this document

If StrPrinter "" Then
With WordApp.Dialogs(97) ' 97 - wdDialogFilePrintSetup
.Printer = StrPrinter
.DoNotSetAsSysDefault = True
.Execute
End With
End If


WordDocM.PrintOut
'If strOutDocName "" Then
'wordApp.ActiveDocument.Close (False)
' when we print...we *always* close the docuemnt..

WordDocM.Close (False)

'End If

WordApp.Visible = True

End If


MyPbar.HideProgress

Set WordApp = Nothing
Set WordDoc = Nothing
Set WordDocM = Nothing
Set MyPbar = Nothing

DoEvents

' If bolShowMerge = True Then
' WordApp.Dialogs(676).Show 'wdDialogMailMerge
' End If

Exit Function

CreateWordApp:
' this code is here to use the EXISTING copy of
' ms-access running. If getobject fails, then
' ms-word was NOT running. The below will then
' launch word
Set WordApp = CreateObject("Word.Application")
Resume Next

End Function

Function RidesEditTemplate(strWordDoc As String, strSaveDir As String)


' Opens a word doc in mail merge mode

Dim WordApp As Object
Dim WordDoc As Object

clsRidesPBar.ShowProgress
clsRidesPBar.TextMsg = "Launching Word...please wait..."

On Error GoTo CreateWordApp
Set WordApp = GetObject(, "Word.Application")
On Error GoTo 0

Set WordDoc = WordApp.Documents.Open(strWordDoc)
WordApp.Visible = True
'*-
WordDoc.MailMerge.MainDocumentType = 0 ' wdFormLetters = 0

WordDoc.MailMerge.OpenDataSource _
Name:=strSaveDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0, _
Connection:="", SQLStatement:="", SQLStatement1:=""
'*-

'AppActivate "Microsoft Word"
WordApp.Activate
WordApp.WindowState = 0 'wdWindowStateRestore

clsRidesPBar.HideProgress

Exit Function

CreateWordApp:

Set WordApp = CreateObject("Word.Application")
Resume Next


End Function
Function RidesNewTemplate(strSaveDir As String)

' ask user for template name to create
'
' Parms:
' strSaveDir = full path of dir is (includes the last backslash

Dim strNewName As String
Dim WordApp As Object 'Word.Applicaton
Dim WordDoc As Object 'Word.Document

strNewName = ""
strNewName = InputBox("What name for new template" & vbCrLf & vbCrLf & _
"(Enter name with no file extension)", _
"Create New Word merge Template")

If strNewName = "" Then Exit Function

' get work object...

clsRidesPBar.ShowProgress
clsRidesPBar.TextMsg = "Launching Word...please wait..."
clsRidesPBar.Pmax = 4
clsRidesPBar.IncOne

On Error GoTo CreateWordApp
Set WordApp = GetObject(, "Word.Application")
On Error GoTo 0

clsRidesPBar.IncOne

Set WordDoc = WordApp.Documents.Add

WordDoc.MailMerge.MainDocumentType = 0 ' wdFormLetters = 0

'MsgBox strSaveDir

WordDoc.MailMerge.OpenDataSource _
Name:=strSaveDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0, _
Connection:="", SQLStatement:="", SQLStatement1:=""

clsRidesPBar.IncOne

' write doc to disk....
WordDoc.SaveAs FileName:=strSaveDir & strNewName, _
FileFormat:=0, _
LockComments:=False, Password:="", AddToRecentFiles:=False, WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:=False


clsRidesPBar.IncOne

WordApp.Visible = True
'AppActivate "Microsoft Word"
WordApp.Activate
WordApp.WindowState = 0 'wdWindowStateRestore

clsRidesPBar.HideProgress

Exit Function

CreateWordApp:

Set WordApp = CreateObject("Word.Application")
Resume Next

End Function



Function MakeMergeText(frmF As Form, strOutFile As String) As Boolean

' build our merge file, and write a simple "csv" file to disk

Dim OneField As DAO.Field
Dim strFields As String
Dim strData As String
Dim intFile As Integer
Dim vField As Control
Dim t As Variant

If frmF.RecordSource "" Then

If frmF.RecordsetClone.Fields.Count > 0 Then
For Each OneField In frmF.RecordsetClone.Fields

If strFields "" Then strFields = strFields & ","
strFields = strFields & qu(OneField.Name)

If strData "" Then strData = strData & ","
strData = strData & qu(frmF(OneField.Name))

Next OneField
End If
End If


For Each vField In frmF.Controls
If vField.ControlType = acTextBox Then
' if it is a text box, then include in field list

If vField.ControlSource = "" Then

If strFields "" Then strFields = strFields & ","
strFields = strFields & qu(vField.Name)
' now add data from this box

If strData "" Then strData = strData & ","
strData = strData & qu(frmF(vField.Name))

Else
' text box is bound, but ONLY include if it is NOT in the field list
On Error Resume Next
t = frmF.RecordsetClone.Fields(vField.Name).Name
If Err.Number = 0 Then
' field name is in reocrd set...skip
Else
Err.Clear
If strFields "" Then strFields = strFields & ","
strFields = strFields & qu(vField.Name)
' now add data from this box

If strData "" Then strData = strData & ","
strData = strData & qu(frmF(vField.Name))
End If

End If
End If
Next vField

'delete the out file if there
On Error Resume Next
Kill strOutFile

' now open file...

On Error GoTo exit1
intFile = FreeFile()
Open strOutFile For Output As intFile
Print #intFile, strFields
Print #intFile, strData
Close intFile

MakeMergeText = True
Exit Function

exit1:

MsgBox "Can't make merge file in directory called word" & vbCrLf & _
"The Word template may already be in use. Try closing word first." & vbCrLf & vbCrLf & _
"Make sure a directory called Word exists" & vbCrLf & _
"path name was " & strOutFile & vbCrLf & vbCrLf & _
"Please create a word directory, exit word and try again", vbCritical, "no word directory, or in already in use"

MakeMergeText = False


End Function




Okay,

This is a little complex dealing with relationship and updating the information as appropriate between a table and itself within a compound query on a subform. There are many relationship here, (including one from a table to itself), but before you go "what the???" i'll try to explain, and eventually my question will become clear.


	Code:
	Table_System
  Field-> SysID (Auto)
  Field-> Name (Text)

Table_Gate
  Field-> ID (Auto)
  Field-> System (Long)
  Field-> Destination (Long)
  Field-> Location (Text)

Primary Relationship
  Enforced Integrity: Table_System.SysID->Table_Gate.System (OneToMany)

This is the initial break down of the setup. So it appears to be pretty simple on the surface. However, the complication is in the relationship of Table_Gate.Destination. I have that setup as such:

	Code:
	Data
Table_Gate
  Field-> Destination 
     Type: Combo Box, RowSource = SELECT Table_Gates.ID, Table_System.Name FROM Table_System INNER JOIN Table_Gate ON
Table_System.SysID=Table_Gates.System; 

Relationship
  Table_Gate.Destination 11 Table_Gate_1.GateID

So, This works, mind you, quite well in fact. Because I can Create a Query from this relationship to provide a very distinct link between One Gate in a System and another Gate in a different System. Which is exactly what i want it to look like. For As I am looking at a form with System X it will have a Gate that points to another Gate in System Y. And since the two gates are unilaterally mutually inclusive, there must ALWAYS be a pair of gates. Now my Gate Form is based upon this Query:

	Code:
	Query_GateDestination
SELECT Table_Gate.ID, Table_Gate.System, Table_Gate.Location, Table_Gate.Destination, GateDest.ID AS DestID, GateDest.System
AS DestSystem, GateDest.Location AS DestLoc, GateDest.Destination AS Origin
FROM Table_Gate INNER JOIN Table_Gate AS GateDest ON Table_Gate.Destination = GateDest.ID;

Seems a little confusing, but the joy of this, is that when i add a new record, i can set the Starting System and Location, and in the same record, select the GateDest.DestSystem for the eventual link. However this is where my problem lies. As i Change the GateDest.DestSystem, The AutoNumber ID for GateDest.ID is created, but it is not listed in the Gate.Destination, nor is the newly created record listed for the GateDest.Origin.
So, I figure this is an Update/Requery problem. For, as I have experience by following the exact process I described for entering a new Gate, the moment I close the Query and Re-Open it, Both New Records Exist, except the GateDest.Origin is not set, though the Gate.Destination IS.

So On a Form based upon Table_System, I have subForm based upon Query_GateDestination (Linked master to child at the SysID->System relationship). What I need to know, is where exactly to put the "update" or "requery" in for the manipulation/addition of the Gates. Since the Gate.System is automatic to the current System I'm viewin in the Parent Form, I need to set the GateDest.DestSystem. UPON setting this, the Query in the background sets up to create 2 unique entries.
Gate A -> B & Gate B -> A. However, in my Gate.Destination and GateDest.Origin Combo Boxes I cannot Select those values UNTIL i close out the form and reopen it.
So my options as I see it are as follows:GateDest.DestSystem->On_Change: Write a handler that Updates the Record and Requeries the RecordSource for the Sub form Catch the SubForm.Before/After Update event and use a sql command to update the Destination and Origin values. Use a command button to manuall update the record and force a requery. Suggestions???
Thanks For the Help in advance
Sincerely,
Jaeden "Sifo Dyas" al'Raec Ruiner




Hi guys,

I've made leaps and bounds with my current little project today, but there's a frustrating error which is keeping me from testing my procedure.

I have a form which contains 2 controls: a button "btnMailIt", and a subform, "qryMessagesMerge subform".

MsgBodyText is a text field in the subform.

I have some code to send report snapshots in a loop to names in a list. I decided to have all the info for the SendObject method in this subform for simplicity's sake, thus other subform fields include MsgSubject and EmailAddress. I wasn't sure it was possible to have merged fields within the MessageBody, so for each record, there is a separate MessageBody record customised to suit the recipient.

My problem is this: when the code tries to establish the MessageBody field, it produces an error. Below is the code up to and including this error. I know a version of the code worked previously, when the message details (To, Subj, Body etc) were stored in a separate table, so I wondered if it was an issue with subforms? I'm suspicious, though, because the code opens the subform in a new window anyway.

Also, and this might be a way of diagnosing more effectively, I'd like to implement more detailed error reporting. You'll see that currently all error numbers produce the same message: MsgBox "ERROR: Message body text field " + MsgBodyTextField + " does not exist or is empty." I want to put in lines e.g.


	Code:
	If Err.Number = 1 Then 'do this
If Err.Number = 2 Then 'do that etc.

But where can I find a list of error numbers and messages for SendObject?

Thanks for your patience!

-Abby



	Code:
	Option Compare Database
Option Explicit
      

Const MsgBodyTextField As String = "MsgBodyText"    ' Name of form field in message table containing message text
Const MsgSubjectField As String = "MsgSubject"      ' Name of form field in message table containing message subject
Const RecordsFormName As String = "qryMessagesMerge subform" ' Name of form that displays table of records which will be used
in mail merge
Const EmailAddressField As String = "EmailAddress"  ' Name of table field in mail merge record table that contains email
addresses

' Name of error log file. 
Const ErrorLogFile As String = "S:SalesMDBProspects Email Merge LogsMailMergeErrorLog.txt"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Const ETYPE As Integer = 0              ' Index of element in MsgElem array that indicates element type - field name or chunk
of literal text
Const ETEXT As Integer = 1              ' Index of other element in MsgElem array. Contains actual field name or chunk of
literal text

Const FIELDNAME As Boolean = True       ' Code goes in ETYPE element to indicate text represents a field name
Const MESSAGECHUNK As Boolean = False   ' Code goes in ETYPE element to indicate text is literal message text

Const FAILURE As Boolean = False        ' General failure return code
Const SUCCESS As Boolean = True         ' General success return code

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Code executed when you click the Mail It button
Private Sub btnMailIt_Click()

Dim MsgElem() As Variant
Dim strMsgToSend As String, _
    strT As String
Dim rst As Object
Dim fld As Field
Dim obj As AccessObject
Dim ctl As Control
Dim bFoundForm As Boolean, _
    bWasClosed As Boolean, _
    bSendError As Boolean, _
    bHadErrors As Boolean
        
    ' Ensure form data is in sync with underlying table
    Me.Refresh
        
    'Ensure the form that contains the mail merge records exists. Open it if necessary
    bFoundForm = False
    bWasClosed = False
    
    For Each obj In Application.CurrentProject.AllForms
        If obj.Name = RecordsFormName Then
            bFoundForm = True
            If obj.IsLoaded = False Then
                DoCmd.OpenForm obj.Name, acFormDS
                bWasClosed = True
                DoEvents
                Exit For
            End If
        End If
    Next

    If Not bFoundForm Then
        MsgBox "Cannot find form " + RecordsFormName + ". Aborting"
        Exit Sub
    End If
        
    ' Ensure all required fields exist
    On Error Resume Next
    
    ' Message body text field. Content is further validated in GetMessageText function
    strT = Me.Controls(MsgBodyTextField)
    If Err.Number  0 Then
        MsgBox "ERROR: Message body text field " + MsgBodyTextField + " does not exist or is empty."
        If bWasClosed Then DoCmd.Close acForm, obj.Name, acSaveNo
        Exit Sub
    End If





Hello. I have searched and found many posts related to my question but still cannot find the way to do it and as I am new to access and this forum, so need something VERY simple to follow.

I have
Table called "Products"
Field 1= "Product ID" which is a text field (PK) but numbers are used (ie 1 -20)
Field 2= Products -showing our list of 20 products

When I enter a new product, currently I have to look in the table to find the last ID used then use the next one available.
I have created a form to be used for data entry to enter new products

What I am trying to do is :- 1, have the form open at data entry level but still able to scroll and see all records and 2, Have the form auto generate the next number available. For example, I have 20 products entered so when the form opens to enter a new product, the ID is automatically at number 21.
I know absol nothing about coding. I have looked at other suggestions on the forum incl the DMAX +1 and having tried and not worked, I wondered if its because the field is a text field or I am trying to insert the code in the wrong place. Thank you in advance for any help and I apologise for my lack of knowledge.




Think I posted this in "Introduce yourself" als so apologies for that

Hello. I have searched and found many posts related to my question but still cannot find the way to do it and as I am new to access and this forum, so need something VERY simple to follow.

I have
Table called "Products"
Field 1= "Product ID" which is a text field (PK) but numbers are used (ie 1 -20)
Field 2= Products -showing our list of 20 products

When I enter a new product, currently I have to look in the table to find the last ID used then use the next one available.
I have created a form to be used for data entry to enter new products

What I am trying to do is :- 1, have the form open at data entry level but still able to scroll and see all records and 2, Have the form auto generate the next number available. For example, I have 20 products entered so when the form opens to enter a new product, the ID is automatically at number 21.
I know absol nothing about coding. I have looked at other suggestions on the forum incl the DMAX +1 and having tried and not worked, I wondered if its because the field is a text field or I am trying to insert the code in the wrong place. Thank you in advance for any help and I apologise for my lack of knowledge.




I have a form which is not working as intended (I have attached the form to this post)

Description of what the form is supposed to do: When the user selects the Site from a list (the list of sites is populated by running a query against the table 'Sites'), the patients enrolled at that site get populated in the patient combo box. Similarly when the Patient is selected, the associated admit dates for that patient get populated in the admit date combo box.
The main form is designed to have Site, Patient and Admit Date (controls above the label DATA ENTRY FORMS). The sub form (lets say 1) has all the controls except for line number, REV.CD, Description and total charges, which is a sub form (lets say sub form 2) in itself under sub form1. The sub form 1 and sub form 2 are bound to two individual tables. When the user selects the Site, Patient and Admit Date from the main form, all the fields in the sub form1 and sub form2 get automatically populated. (There are no duplicate records for same Site, Patient, Admit Date, Discharge Date and Type of Bill, meaning there cannot be two bills of type 100 associated for same Site, Patient, Admit Date and Discharge Date). The tables bound with sub form 1 and sub form 2 are linked by Site, Patient, Admit Date and Type Of Bill (These fields are listed as Master Child links in the property window of sub form2 in sub form 1).

The problem: After selecting the Site, Patient, Admit Date in the main form, and when the fields in the Sub Form1 and Sub Form 2 get populated, I am not able to add a new record to the associated tables by selecting a different type of bill from the Type_Of_Bill combo box. When I select a different bill type (which is not already present in the table, meaning adding a new bill)
the already existing record in the table (s) is getting updated (all the fields in the sub form are not being reset to blank, they have the values from the bill first displayed). What I want the form to do is, when I select a different bill type which is not present (that is I am entering it for the first time) I should be able to add a new record to the tables.

As you can see from the way I have explained, I am new to access, never worked on it before, its not my forte. It's just something I have to do apart from my regular work.Forgive me if this is not clear.




I am having some problems with an Access database, obviously.. relatively new at the whole thing, so it's quite a patched-up mishmash, and I would be happy to expand on the intended structure, but it would all come together if there's a way to use a form-displayed GUID as a record source criteria.

Essentially, I can do the following:

Form 1 with autonumber GUID 1 has a text field on it which is equal to the autonumber GUID 1 field for the current record. This looks like a series of random characters, nothing like the long number, and it's set to be invisible. It then works, on a subform, to set record source criteria to include this text field as a way to delimit subform 2 by (non-autonumbered) GUID1.

However, what does not work is:

Form1 has an unbound lookup list that includes GUIDs as one of the things displayed. These are displayed properly, as a long number. I can set a text field equal to the value of this on the main form, and see the same long number. But I cannot then reference this text field as a criteria for the subform record source, and neither can I reference the lookup list directly. Essentially, it works when the text field is showing squiggly numbers, but not when showing the 'real' GUID.

Is there a way to convert the nice-and-tidy looking GUID from an unbound lookup list to something usable as a record source criteria? Or is this always possible, and I am just missing something elsewhere? I can do this through VBA for a _filter_ criteria with the ={guid " & guidvariable & "}))" thing, but haven't found a way for record sources.


The structure is rather convoluted.. would be happy to explain if there's no other way to do it. Replies greatly appreciated.




Alright, I was asked to make a database to hold answers to a graduation survey. The answers will be used mostly for statisical purposes.

As it stands I've designed the form in microsoft word (the actual survey) which I want to be distributed via email or something similarily convenient. I was initially going do it with a pdf form, except that I discovered people with reader cannot save the filled in form. Useless adobe. I was forced to switch to microsoft word by my inability to find a better solution. I looked at html forms but I don't think I can make those work for my situation. I need whatever I use to be both very convenient to fill out (to maximize the number of responses), as well as to cause a minimal number of potential technical problems for the person filling it out.

As for the tables where the information will be stored, I am not sure how I should design it. The survey itself has aproximately 40 fields for different student information, answers, etc. Some examples are

StudentID, firstName, lastName, areaCode, phoneNumber, Degree, Art, Sciences, Commerce, etc.

StudentID is a unique identifier for each student so easy to make that a primary key btw.

The problems I run into are partly because of the limitations in the way I am distributing the form. I do not want to use macros in microsoft word because I believe that will make 95% of the people unable or unwilling to fill the form out. This leaves me with a lack of functionality that I must make up for on the database end.

For example, I cannot do a cascading menu without macros in microsoft word (as far as I know), and the list box is limited to 25 items. There are 30 majors, so I broke it into 3 categories, Arts, Sciences, and Commerce. However on the database end the one filled out must be found and put into the "Major" field. I figure it should work if I just make an append query to append all 3 fields into one field since the other 2 should be blank. Have yet to try it though.

My problem with all of this is that when this database is being used I am not going to be around. So basically I need help either eliminating steps, or automating the steps to take the information from the form to the database tables.


------------------------------------------------------------------------

Right now the steps required would be something along the lines of:

I) Microsoft word form is sent out to grads, filled out form is sent back.

II) Form information is manually saved to a text file (options>save form info) in format something like "info0", "info1", "info2" etc.

III) The text file is manually imported into access to (dont know where. Possibly a table containing all information in the original format, or else an otherwise empty table) Not sure how to make this simple.

IV) The single survey worth of information is appended to the last row of a table containing the other survey information, as well as some fields combined via queries.

At which point the information is in a useable format.



Things I could use help with:

1) Is there any better way/program I could make the survey that would make my work in Access easier?

2) How many tables should I be using? One seems convenient, since it isn't being used for any other purpose and there is a seperate database for student information etc (not connected to this one at all) so this is self contained..

3) How to greatly simplify this process for the user when they are importing answers?

4) How to do a query to put the three fields into one, and if I can combine this with step 5?

5) The best way to go about adding the new survey answers into the table(s) containing existing survey answers.

The university I'm doing this for is quite small btw and it seems quite unlikely the lifetime of this database will be such that it sees 1000 seperate survey entries.

Any help is much appreciated, I wasn't hired for this and I have limited experience with databases in general heh. The hard part doesn't making it though, it's making it so they can use it.

Sorry for length




I've made a form with list boxes on, the user's selections from which are taken as parameters for a query, created by VBA code.

I want to add a final list box to the form from which the user can select which of the available fields they want to see in the query output (i.e. has the same effect as checking/unchecking the "Show" checkbox for each field on the standard query design grid).

I have written the following code to try and do this, but it isn't working. I'm very new to VBA and wonder if anyone can help me out by showing me what's wrong?


	Code:
	'Build Field List
If Me!lstFieldList.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lstFieldList.ItemsSelected
        strFieldList = strFieldList & "[" & Me!lstFieldList.ItemData(varItem) & "], "
    Next varItem
    strFieldList = Left(strCriteria, Len(strCriteria) - 2)
Else
    strFieldList = "'*'"
End If
     
'Create Query String
strSQL = "SELECT Centres." & strFieldList & " " & FROM Centres " & _
    "Where " & strCriteria & _
    " And " & strCriteriaCtr & strSortOrder & ";"

Running the above gives an error, and the section under "'Create Query String" is highlighted in debug mode so I guess the error must lie there? Either that, or the earlier part where strFieldList is being create is generating something which strSQL cannot interpret.

Many thanks in advance if you can help me.

Gary





I have been working on a normalized db for the past few months and it works wonderfully. But now I need to find a way to create a form so that the rest of the company can update their data.

My Table has the 5 following fields. Also listed is an example of the types of data I have there

StoreID_Catagory_Customer Year_Month_MixPercentage
IN00001_Japanese_____2005_____Jul_______.01______
IN00001_Japanese_____2005_____Aug______.02______
IN00001_Japanese_____2005_____Sep______.01______
IN00001_Chinese______2005_____Jul_______.35______
IN00001_Chinese______2005_____Aug______.25______
IN00001_Chinese______2005_____Sep______.26______
IN00001_Japanese_____2006_____Jul_______.01______
IN00001_Japanese_____2006_____Aug______.02______
IN00001_Japanese_____2006_____Sep______.01______
IN00001_Chinese______2006_____Jul_______.35______
IN00001_Chinese______2006_____Aug______.25______
IN00001_Chinese______2006_____Sep______.26______

As you can see I have multiple StoreID's for one store, IN00001. My db goes up to IN00419 so it ends up displaying 29,664 records. There are 4 different categories (Japanese, Chinese, Local, and Other). Until now everything has been taken from a datasheet and put into an Update Query but now that other people have to quickly access the system I need a slick interface.

What I need is a way to have a form that can update the table and add new entries that looks like this in the form:

IN00001____2005________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04

IN00001____2006________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04

I hope this gives you a clearer picture. I need to be able to update 96 entries at a time (12months, 4 customer types, 2 years) but do it in an efficient manner. The table only has 5 fields so I need to figure out how to display multiple entries on the same form. I also need a way to add new entries. In other words for me to add another StoreID such as IN00420 then I would have to enter in a value for every field for 96 records in the table. When in reality I should only need to update the mix percentages since the months and years remain constant. HELP!!

Any help would be much appreciated. Due to the variable nature of the db I cannot establish very many one-to-one relationships. So therein lies another problem.


Page 1 of 3.
Results 1...20 of 53