Can I go to the record if duplicate of it is found? 1

[Scootman]

Hi all (and everybody else),

I'm a first time poster, long time post-reader....so be gentle please. This is a long message, but its always better to explain as much as you can to save others time (or so the theory goes).

I am in a quandary wondering how to fix this problem. I looked everywhere on this forum for an answer, but oddly enough, all I could find are posts where people want a customized error message when a duplicate record is found. Instead of a message, I would like the form to be able to go to the record that already exists. Here goes with the specifics of my problem:


I have a database with two forms,

Form Name | Bounded To?
--------------------------
1. Calendar | unbound
2. PhoneCalls | table named "Calls"


The Calls table only has one field, "Date". You can probably guess what data that field contains.


Here's what I've done already:

I have an unbound text field in the Calendar form. This text field is named "txtdate". When I click on a date in the calendar, the txtdate field is populated with the date. A command button becomes visible at the same time that allows me to create that date in another form (the PhoneCalls form). Here's the code....

-------------------------------------------------
Private Sub cmdEnter_Click()
DoCmd.OpenForm "PhoneCalls", acNormal
DoCmd.GoToRecord acDataForm, "PhoneCalls", acNewRec
Forms!PhoneCalls!Date = Forms!Calendar!txtpr
End Sub
-------------------------------------------------


Here's what I need help with:

1. Replace the code above with a code that looks for duplicates in the PhoneCalls form (or the Calls table). Again, this code is coming from the unbound Calendar form. If this setup needs to change, please let me know.

2. When a duplicate is found, I want the PhoneCalls form to go to the record with the date in question.

3. If a duplicate isn't found, have the form go to a new record ready for creation.

4. In case it makes a difference, my database exists in both Access 2000 and 2002.


Again, I tried looking at the other threads, but they just don't apply to me since they deal with forms that are both bounded and I haven't seen a thread where anyone wants to go to the record if it exists.


Thanks in advance for any attempts to solve my problem, I really appreciate it.

Scootman

 

[WhiteZiggy]

Scootman,

I don't know if this is exaclty what you want but here goes. You want to know if duplicates are there for a specific date (or any field) and go to the record?

Could you do it by running a quick query that populates a small recordset (size 2) and if the recordcount >1 then goto that record number?

IE..

strSQL= "Select Date,Name where date = #1/1/03# order by date"
rsNames = openrecordset (strSQL)

If rsnames.count < 1 then
DoCmd.OpenForm &quot;PhoneCalls&quot;, acNormal
DoCmd.GoToRecord acDataForm, &quot;PhoneCalls&quot;, acNewRec
else
rsnames.movelast
'Get your record you ID for the record you want
'Then place code to code to go to that ID on the form
...ETC..


This may not be optimal, but would work for you in the meantime of finding an optimal solution i believe.

Hope This Helps,
WhiteZiggy

 

[Scootman]

Cool, thanks!

Sounds like a plan to me (couldn't hurt to try). I'm off to work right now, but I'll see if your idea works for my problem. After a lot more searching on this forum, I think I might have come across a thread that helps me. I won't be able to try it out until I get to work, but I'll be sure to post a reply on here either way on my progress. I'll try the idea mentioned above first since it looks the most promising so far.

Thanks again WhiteZiggy.

-- Scootman

 

[Scootman]

WhiteZiggy,

I wasn't able to do anything with your code. Probably because its using queries and SQL. I've always steered away from SQL since its not my style of programming (in other words, I'm clueless about it). Your code was also using the assumption my dates are in the ##/##/## format. My date fields are in the text format of, &quot;January 1, 2003&quot;. So they can't be read as a number string.

I also tried modifying other codes I found in this forum. They weren't helpful either since none of them were used to search from an unbound form to a bound form and they also weren't helpful because I couldn't find one where a user needed to go to the record if it already existed (like I mentioned in my original post, everybody just seems concerned about message boxes).

Looks like my first big problem with VBA in the 2 years I've been using it is an unsolvable one. Even the help files in Access weren't helpful (which is a first for me).

I do appreciate your help though and anyone else's who read my problem and tried to think of a solution.

I'll just try to think of another way to archive my information.

- Thanks again

Scootman

 

[WhiteZiggy]

Scootman,

The date format does not matter. When in the &quot;code&quot; module. Hit help, it brings up visual basic help instead of access and is VERY GOOD for examples.

To change the date you can do

: Format([Date],&quot;Short Date&quot which will make your date read as 7/17/03 but wont change your date value. Dates are stored in access really as longnumbers. It is the formating that lets you read it the way you want.

If you want a great way to write sql, do the query that gets you the right recordset then in the bar to run it click SQL instead of design view. It will show you the SQL for that query (its a great 'cheat') ).

You can use a query to get the recordset too instead of SQL. That is just the easiest way for me to show you.

If you take a look at the VB Help, I believe you will be amazed how much you do know..

The key for the help is to be in the CODING part when you hit it.

(IE. Hit the on envents button in design mode and it says do you want to make a macro or code. Select code, then hit help once there.)

Hope this helps,

WZ