Prev: How to send data from local database to MySQL db on website
Next: workgroup join problems
From: Craig on 8 Jul 2010 11:26
I’m trying to make a conditional macro that will check a table to make
sure there isn’t already a record with the same ID and Entry #. If
there is a record, it should give you a message box warning of
duplicate entry. If there isn’t any returned values then it should run
an append query.

So conceptually speaking

If Query1 > 0 then Error Message: “Already entered”
If Query1 Is Null then AppendQuery

Query 1:
(SELECT Count(Tbl_Assessment.ID) AS CountOfID
FROM Tbl_Assessment
WHERE (((Tbl_Assessment.Entry)=1))
GROUP BY Tbl_Assessment. ID, Tbl_Assessment.Entry, [Forms]![Form1]!
[ID];)

AppendQuery:
INSERT INTO Tbl_Assment ( Trial, ID )
SELECT Tbl_Lookup.Lookup_Value, [Forms]![Form1]![ID] AS ID
FROM Tbl_Lookup
WHERE (((Tbl_Lookup.Group)=52));

How would I go about doing this in a macro?
From: Salad on 8 Jul 2010 12:30
Craig wrote:
> I�m trying to make a conditional macro that will check a table to make
> sure there isn�t already a record with the same ID and Entry #. If
> there is a record, it should give you a message box warning of
> duplicate entry. If there isn�t any returned values then it should run
> an append query.
>
> So conceptually speaking
>
> If Query1 > 0 then Error Message: �Already entered�
> If Query1 Is Null then AppendQuery
>
> Query 1:
> (SELECT Count(Tbl_Assessment.ID) AS CountOfID
> FROM Tbl_Assessment
> WHERE (((Tbl_Assessment.Entry)=1))
> GROUP BY Tbl_Assessment. ID, Tbl_Assessment.Entry, [Forms]![Form1]!
> [ID];)
>
> AppendQuery:
> INSERT INTO Tbl_Assment ( Trial, ID )
> SELECT Tbl_Lookup.Lookup_Value, [Forms]![Form1]![ID] AS ID
> FROM Tbl_Lookup
> WHERE (((Tbl_Lookup.Group)=52));
>
> How would I go about doing this in a macro?

This link will show you the basics. The queries should be saved if you
used Dcount(). The Dcount() would be on the 1st 2 lines; one to display
the error message, the next to stop the macro. The following lines to
run the append query.
http://www.learnaccessnow.com/chap18c.html
From: Craig on 8 Jul 2010 14:18
For anyone who runs across this in the future I was passing the form
values to the DCount function and was having problems it not working.
You need to check the quoting which is explained here:
http://allenbrowne.com/ser-66.html

From: Salad on 8 Jul 2010 14:46
Craig wrote:
> For anyone who runs across this in the future I was passing the form
> values to the DCount function and was having problems it not working.
> You need to check the quoting which is explained here:
> http://allenbrowne.com/ser-66.html
>
In reality, do you really nead a "group by" query?

All you are really looking for is if there's an Entry = 1 per your query
in the post. In fact, you could create display an error message when a
person enters a 1 into the Entry field.

SELECT EntryID FROM Tbl_Assessment WHERE Entry = 1 would work per the
original post if you didn't want to trap the data entry.
 | 
Pages: 1
Prev: How to send data from local database to MySQL db on website
Next: workgroup join problems