I have two tables 1.tblmonth which holds two fields txtmonth and txtqtrlabel
and 2. tblmain which holds a number of fields but in particular a field
called txtqtrlabel2. The two tables are linked on txtqtrlabel and
txtqtrlabel2. I have a main form based on tblmonth which has a subform based
on tblmain, the two forms are also linked on the same fields.
The data entered into txtmonth could be "30/09/04" and the txtqtrlabel data
would be "September 2004" Both the controls are comboboxes with NotInList
events. The code for the NotinList event for txtmonth is:
Private Sub cmbmonth_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new date?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
The code for txtqtrlabel is the same apart from the field references.
PROBLEM - When entering a new record I enter the txtmonth first then the
txtqtrlabel but as soon as I tab out of the main form to the subform I get a
long message which says I cant make the changes I requested because they
would create duplicate values and I should change the data which contains
the duplicate data. None of the fields in tblmain are restricted to
duplicates but the field txtmonth is because in that table I only want the
date to appear once. So there should be no duplicates in tblmonth but the
field txtqtrlabel can appear more than once in the tblmain.
Can anyone help here? I want the user to be able to add dates to the
txtmonth control as they go along by adding any new ones to the list, hence
the code.
Sorry this is so long!!!!
TIA
Tony Williams
9  2291 
Tony, to solve the problem I would recommend a structure change. The month
label (i.e. September 2004) can be computed from the date, it doesn't need
to be stored. You could make the computation in a query feeding each form,
if desired, and link on that calculated field. To fill the Row Source of the
combo box so that it doesn't contain duplicate values, you would also use a
query, create the value using a calculated field, and set the query to only
show unique values.
Example:
SELECT DISTINCT Format([Field2],"mmmm yyyy") AS MonthYear,
DatePart("yyyy",[Field2]) AS Expr1, DatePart("m",[Field2]) AS Expr2
FROM Table1
ORDER BY DatePart("yyyy",[Field2]), DatePart("m",[Field2]);
The two calculated fields Expr1 and Expr2 are included solely for sorting.
The format function will return a text value and so it won't sort as
expected (i.e. chronologically), it will sort in alphabetic order. The last
two fields return numeric values for the year and month and so will sort
correctly.
The DISTINCT keyword is what removes the duplicates. [Field2] is a Date/Time
field type.
--
Wayne Morgan
Microsoft Access MVP
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:co**********@titan.btinternet.com... I have two tables 1.tblmonth which holds two fields txtmonth and
txtqtrlabel
and 2. tblmain which holds a number of fields but in particular a field
called txtqtrlabel2. The two tables are linked on txtqtrlabel and
txtqtrlabel2. I have a main form based on tblmonth which has a subform
based
on tblmain, the two forms are also linked on the same fields.
The data entered into txtmonth could be "30/09/04" and the txtqtrlabel
data
would be "September 2004" Both the controls are comboboxes with NotInList
events. The code for the NotinList event for txtmonth is:
Private Sub cmbmonth_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new date?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
The code for txtqtrlabel is the same apart from the field references.
PROBLEM - When entering a new record I enter the txtmonth first then the
txtqtrlabel but as soon as I tab out of the main form to the subform I get
a
long message which says I cant make the changes I requested because they
would create duplicate values and I should change the data which contains
the duplicate data. None of the fields in tblmain are restricted to
duplicates but the field txtmonth is because in that table I only want the
date to appear once. So there should be no duplicates in tblmonth but the
field txtqtrlabel can appear more than once in the tblmain.
Can anyone help here? I want the user to be able to add dates to the
txtmonth control as they go along by adding any new ones to the list,
hence
the code.
Sorry this is so long!!!!
TIA
Tony Williams
Thanks Wayne. Can I deal with the first part the calculated field. I tried
using Format to calculate the txtqtrlabel Control but as a calculated field
it was not stored. This seemed to cause me problems with creating reports
because the two databases were linked on those fields as the calculated
field wasn't stored there was no link with the records.
How do I overcome this if I want to write reports which is based on a query
on both tables when there is no data to link them with?
Sorry if the answer is obvious!!
Thanks
Tony
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:R3******************@newssvr33.news.prodigy.c om... Tony, to solve the problem I would recommend a structure change. The month
label (i.e. September 2004) can be computed from the date, it doesn't need
to be stored. You could make the computation in a query feeding each form,
if desired, and link on that calculated field. To fill the Row Source of
the combo box so that it doesn't contain duplicate values, you would also use
a query, create the value using a calculated field, and set the query to
only show unique values.
Example:
SELECT DISTINCT Format([Field2],"mmmm yyyy") AS MonthYear,
DatePart("yyyy",[Field2]) AS Expr1, DatePart("m",[Field2]) AS Expr2
FROM Table1
ORDER BY DatePart("yyyy",[Field2]), DatePart("m",[Field2]);
The two calculated fields Expr1 and Expr2 are included solely for sorting.
The format function will return a text value and so it won't sort as
expected (i.e. chronologically), it will sort in alphabetic order. The
last two fields return numeric values for the year and month and so will sort
correctly.
The DISTINCT keyword is what removes the duplicates. [Field2] is a
Date/Time field type.
--
Wayne Morgan
Microsoft Access MVP
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:co**********@titan.btinternet.com...I have two tables 1.tblmonth which holds two fields txtmonth and
txtqtrlabel
and 2. tblmain which holds a number of fields but in particular a field
called txtqtrlabel2. The two tables are linked on txtqtrlabel and
txtqtrlabel2. I have a main form based on tblmonth which has a subform
based
on tblmain, the two forms are also linked on the same fields.
The data entered into txtmonth could be "30/09/04" and the txtqtrlabel
data
would be "September 2004" Both the controls are comboboxes with
NotInList events. The code for the NotinList event for txtmonth is:
Private Sub cmbmonth_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new date?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Set db = Nothing
Set rs = Nothing
End Sub
The code for txtqtrlabel is the same apart from the field references.
PROBLEM - When entering a new record I enter the txtmonth first then the
txtqtrlabel but as soon as I tab out of the main form to the subform I
get a
long message which says I cant make the changes I requested because they
would create duplicate values and I should change the data which
contains the duplicate data. None of the fields in tblmain are restricted to
duplicates but the field txtmonth is because in that table I only want
the date to appear once. So there should be no duplicates in tblmonth but
the field txtqtrlabel can appear more than once in the tblmain.
Can anyone help here? I want the user to be able to add dates to the
txtmonth control as they go along by adding any new ones to the list,
hence
the code.
Sorry this is so long!!!!
TIA
Tony Williams
You're correct, the calculated field isn't going to be stored. To work
around this for your report, create a query on the table and add the
calculated field to the query. Now, create the query for the report, using
the query with the calculated field as one of the tables instead of the
table itself. You can now create the join in the query on the table from the
other database and the calculated field.
--
Wayne Morgan
Microsoft Access MVP
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:co**********@hercules.btinternet.com... Thanks Wayne. Can I deal with the first part the calculated field. I tried
using Format to calculate the txtqtrlabel Control but as a calculated
field
it was not stored. This seemed to cause me problems with creating reports
because the two databases were linked on those fields as the calculated
field wasn't stored there was no link with the records.
How do I overcome this if I want to write reports which is based on a
query
on both tables when there is no data to link them with?
Hello,
Please excuse me if this is old hat to all. We are in the process of
migrating an older Access database to Access 2002. It makes extensive use
of Macros and in many places it uses the "FormIsLoaded" for several logic
checks.
It appears that formisloaded does not work in access 2002, or we are not
using it correctly for this version of access.
Also, at least for now, we would prefer to stay with the macro
approach rather than take on the task of re-writing them in Visual Basic. I
know, that is what we will have to do eventually.
Can someone assist?
Rich ri**@hocking.net
I suspect that FormIsLoaded is a user defined VBA function that is being
called by the macro. If that is the case, it probably uses DAO. Access 2000
and newer use ADO by default. Some of the components of these two data
access methods have the same name, such as both have recordsets. The use of
these is determined by the References that are set in Tools|References in
the VBA editor. Open this window and see if there is a check mark next to
"Microsoft DAO 3.6 Object Library". If there isn't then check it and click
Ok. If there is a check mark next to an earlier version of DAO, uncheck it
and check the new one and click Ok.
Next, go into the function and look for statements such as
Dim rst As Recordset, db As Database
Dim qdf As QueryDef
They may be defined on one line (as in the first line above) or on multiple
lines. These 3 are probably the most common DAO items you will find. Change
the Dim statements to
Dim rst As DAO.Recordset, db As DAO.Database
Dim qdf As DAO.QueryDef
Specifying DAO will prevent Access from accidentally trying to use ADO
instead. You will need to do this for all of the VBA code in the database.
Other common DAO items you may find are Field and TableDef. There are more.
--
Wayne Morgan
MS Access MVP
"News" <ri**@hocking.net> wrote in message
news:c6*************@news.ee.net... Hello,
Please excuse me if this is old hat to all. We are in the process of
migrating an older Access database to Access 2002. It makes extensive use
of Macros and in many places it uses the "FormIsLoaded" for several logic
checks.
It appears that formisloaded does not work in access 2002, or we are
not
using it correctly for this version of access.
Also, at least for now, we would prefer to stay with the macro
approach rather than take on the task of re-writing them in Visual Basic.
I
know, that is what we will have to do eventually.
Can someone assist?
Rich
ri**@hocking.net
Wayne,
Thanks for the response. I should have stated that we are converting an
Access 2.0 database that we have used for many years to a Access2002. Under
Access 2.0, FormIsLoaded was a built-in Condition that could be used in a
Macro. We selected Access2002 because it is the newest Access that still
uses Macros. I have never actually tried to do anything with VBA, but even
if I did, it appears to me that you would have to put it into a RunCode
Action and there does not appear to be anyplace to put it into a Condition.
Perhaps there is a different condition that is in 2002 that I simply
have not found.
How would you set a Condition in a Macro to return a True/False to
check if a Form Is Loaded ?
Thanks.
Rich
Access 2003 still has macros also, but requires Windows 2000, XP, or 2003
Server to run. I don't see the FormIsLoaded as an option in the newer
macros. There is a VBA option to make the test, but the macro won't accept a
value back from the function (i.e. True or False) so I don't know that it
would do you any good unless you have the macro pass a parameter to the
function to tell the function what to do if the form is open.
--
Wayne Morgan
Microsoft Access MVP
"News" <ri**@hocking.net.invalid> wrote in message
news:tm*************@news.ee.net... Wayne,
Thanks for the response. I should have stated that we are converting
an
Access 2.0 database that we have used for many years to a Access2002.
Under
Access 2.0, FormIsLoaded was a built-in Condition that could be used in a
Macro. We selected Access2002 because it is the newest Access that still
uses Macros. I have never actually tried to do anything with VBA, but
even
if I did, it appears to me that you would have to put it into a RunCode
Action and there does not appear to be anyplace to put it into a
Condition.
Perhaps there is a different condition that is in 2002 that I
simply
have not found.
How would you set a Condition in a Macro to return a True/False
to
check if a Form Is Loaded ?
Thanks.
Rich
The Northwind database has a module named Utility Functions (I think) that
includes a function IsLoaded(), which takes the name of a form as a string,
e.g. IsLoaded("frmSwitchboard") returns True if the form is loaded and false
if not. If you copy this module into your database, you can use the
IsLoaded() function as the condition for macro execution.
To test this, create a macro with the single action BEEP and then in the
Condition column, type
IsLoaded("frmSwitchboard")
or whatever other form name you choose. If the form is loaded and you run
the macro you should get the beep, otherwise not.
The Access 2003 version of the function is this:
Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet
view.
Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function
It has been available in Northwind for all versions of Access that I can
remember, but the code varies from version to version, so it's best to use
the sample code from the version you have installed.
"News" <ri**@hocking.net> wrote in message
news:c6*************@news.ee.net... Hello,
Please excuse me if this is old hat to all. We are in the process of
migrating an older Access database to Access 2002. It makes extensive use
of Macros and in many places it uses the "FormIsLoaded" for several logic
checks.
It appears that formisloaded does not work in access 2002, or we are
not using it correctly for this version of access.
Also, at least for now, we would prefer to stay with the macro
approach rather than take on the task of re-writing them in Visual Basic.
I know, that is what we will have to do eventually.
Can someone assist?
Rich
ri**@hocking.net
I don't know if you want to try this, but there is an option on the tools
menu under Macro to Convert Macros to VBA. Make a backup copy of the file
first.
--
Wayne Morgan
Microsoft Access MVP
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: mark |
last post by:
I've been working on an Access 2000 database for a couple of weeks now. I
took a course in access about a year ago, a crash course, and I learned a
ton, but I didn't touch Access for the year since...
|
by: Jason |
last post by:
Hi,
I was wondering if any could point me to an example or give me ideas on how
to dynamically create a form based on a database table? So, I would have a
table designed to tell my application...
|
by: MyNameIsnt |
last post by:
Can anyone tell me why, when I click on the buttons it register 2
characters on the display?
if you use the right mousebutton it works ok, but the buttons dont
flash??
it works fine without the...
|
by: MFC |
last post by:
Ok, after three C# books, (C# How to Program, Programming in the Key
of C#, and C# Weekend Crash Course) and three weeks, I believe I have
tried everything to make a certain form function...
|
by: Daniel |
last post by:
I'm new to .Net and all of its abilities so I hope this makes sense.
Basically I'm confused on when is the appropriate time to use web forms
controls vs. regular HTML.
For example in ASP...
|
by: Chad |
last post by:
I have a problem that I am desperate to understand.
It involves dynamically adding controls to a Table control that is built as a result of performing a database query.
I am not looking to...
|
by: Tim::.. |
last post by:
Can someone please help....
I'm having major issues with a user control I'm tring to create!
I an trying to execute a sub called UploadData() from a user control which I
managed to do but for...
|
by: Edwinah63 |
last post by:
Hi everyone,
i was wondering if anyone else had observed this and if there is any
workaround or patch.
i have some screens (an mdi child forms) with the buttons &Add, &Edit,
&Save and E&xit,...
|
by: Michael D. Reed |
last post by:
I am using the help class to display a simple help file. I generated the
help file using Word and saving it as a single page Web page (.mht
extension). I show the help file with the following...
|
by: =?Utf-8?B?U2l2?= |
last post by:
I have a form that I programmatically generate some check boxes and labels on.
Later on when I want to draw the form with different data I want to clear
the previously created items and then put...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
