Find Duplicates in two fields against Another two fields

[jal]

Jal,
sorry about this... I just quickly created a database to show what I was trying to do... I should off taken more care...

You maybe right I may not be updating the correct record...

Regards - Paul

Here's another thing I found confusing. A call to DCount is supposed to invoke your column(s) by name. The columns in your table are named "OldAccountNo" and "OldAccountName". But your Dcount call has the TEXTBOX names:

DCount("*", "tbl_temp_Account", "[AWSA_BankAccountNo] = '" & _
Me.AGA_BankAccountNo & "' AND AWSA_BankSortCode = '" & Me.AGA_BankSortCode & "'")

So which columns do you wish to DCOUNT? Since you are checking for dups, I am guessing you want to compare against the old values, maybe something like this:

Dim  WHEREclause as String
WHEREclause =  "OldAccountNo = '" & _
        Me.AGA_BankAccountNo & "' AND OldAccountName = '" & Me.AGA_BankSortCode & "'"
strFoundAccount = DCount("*", "tbl_temp_Account", WHEREClause)
 Set rstRecordset = CurrentDb.OpenRecordset("SELECT * FROM tbl_temp_Account WHERE " & WHEREclause)

 

[jal]

As I suggested earlier, personally I would use unbound forms and textboxes. I would try to do everything in code,. It's very rare that I can get the job done quicker using bound controls.

 

[paul.raeburn]

jal
I have done it this way, because this is what was suggested... I would be intrested in what you mean by "I would use unbound forms and textboxes. I would try to do everything in code,. It's very rare that I can get the job done quicker using bound controls."

Regards - Paul

 

[Pat Hartman]

If the field you are tring to update is in the current record, all you need is a single statement -

Me.SomeField = SomeCalcValue

Jal,
The whole reason for using Access is because of the bound forms and reports. If you are having trouble with controling your app, study the events and properties of forms and controls and you should find that you have better success when you use the correct event.

 

[paul.raeburn]

Pat,
this make sense and works well...

again many thanks

Regards - Paul

 

[jal]

jal
I have done it this way, because this is what was suggested... I would be intrested in what you mean by "I would use unbound forms and textboxes. I would try to do everything in code,. It's very rare that I can get the job done quicker using bound controls."

Regards - Paul

For starters, I cancel any wizards that pop up. I choose Design view when offered a choice. I drop textboxes on forms and do not tie them to a table.

For instance, let's assume the user is a customer who needs to edit his account info. The first thing I would do is drop a textbox on the form from the Toolbox, used as a search field. He would type his name or account number nto the textbox and then maybe click a btnSearch and then I would use the click event as an opportunity to go find his information (pull it into a recordset). I would use the recordset to manually display his information on the form, populating textboxes one by one using code. The user would then edit the textboxes. Then I'd have a btnUpdate for him to click when he is ready to write his edits back to the database.

The form itself would thus have no recordsouce, and the textboxes no control source. Depending on the situation, though, I might have used (to reduce the number of textboxes) a subform which the user can edit. Pretty rare that I'll do that. In sum, I use databinding SPARINGLY.

I work for a very successful company. All they use is Visual Studio, and they virtually never use databinding. They do what I have described above. I imagine the majority of Visual Basic and VB.Net developers do the same.

 

[Mike375]

jal,

The only unbound forms I have ever used are for menus and sometimes to hold data in an unbound text box as a reference while other things are happening. I have seen unbound forms referred to many times on the forum but I am still puzzled as to how to use them. Or perhaps it is a case that the people who use them make the DB in a different way.

How do handle a form opening. For example I might have a menu with a button for Clients one for Personal and one for Prospects. A from opens and is full of records.

What about an index based on a continuous form. I have that with A to Z across the top so as to go to the first record with last name startting at S* etc. The person can see the basic details and if they want to go further they click on Lastname and that goes to that person in the main form.

Are things stilll done that way when the forms are unbound and if so how do you do it. What is the advantage of using unbound forms.

 

[jal]

If the field you are tring to update is in the current record, all you need is a single statement -

Me.SomeField = SomeCalcValue

Very useful tip. I wasn't smart enough to figure that one out on my own, unfortunately. Par for me, I guess.

Jal,
The whole reason for using Access is because of the bound forms and reports.

I'm sure you're right about this. Trouble is, I've been struggling to get used to the Access programming model for a while now but am still finding myself vacillating as to whether it really is much easier to use than unbound forms. Maybe I'll adjust to in time - but I get this nagging feeling that, given how long it is taking me to adjust, maybe it really isn't easier after all? Sometimes I just don't know.

 

[jal]

What is the advantage of using unbound forms.

I can't put my finger on it really. Maybe it's my own stupidity. In trying to help others on this forum solve their bound-form questions, I find their sample databases very confusing. I usually can't figure out what they are trying to accomplish and something tells me I will always find unbound forms easier to comprehend.

In a recent project I had a bound listbox. Access wouldn't let me update one of the records. Why not? Because it was bound to the listbox, so the record was "tied up." So I had to empty the listbox each time I wanted to update the record. I never had this problem in VB.Net.

Clearly, there are pros and cons with the Access model. I do like Access, but maybe I'll like it more once I get more adjusted to it.

 

[Mike375]

In trying to help others on this forum solve their bound-form questions, I find their sample databases very confusing. I usually can't figure out what they are trying to accomplish and something tells me I will always find unbound forms easier to comprehend.

I think that is more to do with the stage the person is at.

But I am still having trouble trying to imagine a DB with unbound forms. Is there a way to duplicate the continuous form?

I get the feeling that the unbound form data base is a "one record" only that is up for view and to go to another record you fill the form with different details. Does work that way?

 

[jal]

Ok, I just visited another thread, and this is an example of what I am saying.
http://www.access-programmers.co.uk/forums/showthread.php?t=163561

To summarize the poster's situation (if I understood it correctly). He has a bound form. The user edits the current record. The table, is in fact, updated. The user then moves to the next record - but if he backtracks to the previous record, the form shows the non-updated data! In post #3, Pat explains the cause of this, and the solution. But if you read Pat'se explanation, you'll hardly find it conceptually simple. (Maybe I'm just a complete idiot). After 800 posts on this forum to date, I would think this sort of thing would be a litle easer to follow if, in fact, "bound forms are dramatically much easier to work with than unbound forms" (as some people seem to insist). Here again, I'm just not sure.

 

[Mike375]

Bearing in mind I did not make a close study of the thread......

But update queries are like that on a form. Often it goes unoticed because the the will be refreshed as a matter of course. For example, I run an update query that refills a Rnd() field with new numbers and then a sort is done so I don't see it.

That poster could do something like:

1) The "click" to close the form first opens an unbound form with textbox and setvalues the records ID number to the textbox on the unbound form.

1) Closes the main form

3) Main form is reopend and the value from the textbox in the unbound form is transferred to an unbound text box on the main form and then a FindRecord is done and the unbound form closed.

But lets say the main form was unbound what would the poster do. Nothing I guess since there is no other record to go to.

 

[jal]

But lets say the main form was unbound what would the poster do. Nothing I guess since there is no other record to go to.

Um......Incorrect. The same logic that loaded the record the first time would load it the second time and, in this case, the record would have been loaded correctly, and there wouldn't even have been a "problem" to solve. In other words that other thread wouldn't exist since, using the methods which I propose, the problem would never have arisen to begin with.

 

[Mike375]

Um......Incorrect. The same logic that loaded the record the first time would load it the second time and, in this case, the record would have been loaded correctly, and there wouldn't even have been a "problem" to solve. In other words that other thread wouldn't exist since, using the methods which I propose, the problem would never have arisen to begin with.

But lets say the bound form only has one record then you close/open.

But also, with many records, as illustrated in my post above I would not have the problem of the other poster.

I think the unbound form/DB is a bit like a car with all the extras stripped out and as such you can't have a problem with the air conditioner because it does not have one.

You won't be able to make a tabular form that was unbound. But as I illustrated above I would not have the problem the other poster had.

The bound form DB is only causing problems because it allows the person to go much further.

 

[jal]

You won't be able to make a tabular form that was unbound

There is some truth in this, but only because Access was designed with a preference for bound forms. In VB.Net, creating unbound tabular forms is standard. (The term "databinding" is used in VB.Net but does not refer to a database connection).

In other words Access forces me to do some databinding (unfortunately) but, as I said earlier, I keep it to a minimum.

Maybe I'll get more bound-oriented with time. At the moment I'm just not sure it is terribly advantageous. But at least you and I can agree that Access provides a great environment for development, any way you look at it.

 

[Mike375]

There is some truth in this, but only because Access was designed with a preference for bound forms.

And that is the key point.

 

[Pat Hartman]

I work for a very successful company. All they use is Visual Studio, and they virtually never use databinding. They do what I have described above. I imagine the majority of Visual Basic and VB.Net developers do the same.

and that's why an experienced Access developer can develop an application in a fraction of the time a .net developer would require I don't want to jerk your chain Jal. I also came from a real programming background and when I took up Access it was confusing to me to have code and property settings all over the place but at some point it just clicked. For example, replacing two dozen lines of code with one is simply a matter of understanding how a bound form works. You don't even have to have fields from the recordsource bound to controls in order to work with them in the form's events (reports are different in this regard - Access rebuilds your query and eliminates any field not bound to a control so you may need hidden fields - this is the confusing part of Access). An example of this is UpdateBy and UpdateDate fields which I put in all my tables but never show on my forms. In the BeforeUpdate event of the form, I populate these two fields with just one line of code each.

Rather than fighting with Access, try to go with the flow. You'll be amazed at how much more productive you will be. The two most important form events are BeforeUpdate and Current. The BeforeUpdate event is the LAST event to run prior to a record being saved so this is where you put your last chance edits such as the ones that check for null values or the relationship between two controls such as FromDate and ToDate. Unless you power off or terminate Access with the task manager, the event ALWAYS runs when you leave a dirty record. People put code in all sorts of events trying to trap errors but this is the ONLY event you really need.

The Current event runs each time the recordset moves to a different record, including a new record. So, this event is generally used to lock or unlock controls or fill in unbound fields.

The way I look at it is - I've written my million lines of code and I don't need the practice So my first thought is a property setting, my second is an update query, and my third is code. I have Access applications with over 20,000 lines of code but the vast majority cap out at a couple of thousand.

One last thing - the .text property in Access is different from the .text property in VB. In Access you want to use the .value property or just omit the property reference entirely since .value is the default. In Access VBA, the .text property is ONLY available when the control has the focus.