Edit form vs New form

[bundy75]

Hi

I currently have a form for entering new clients into, what I thought would be nice is instead of having a new form for editing the clients that when I enter a name into the name textbox it would either load the existing matching record or create a new record if it didn't match any exiting ones. As part of this it would be nice to have the textbox complete any matching names in database.

Is this possible ???? or is there any nicer way of acheiving this without two seperate forms. Trying to have a simple user interface.

Thanks

[pkstormy]

See here for an example: https://www.accessforums.net/code-re...ames-7680.html


I have a function I call which checks for duplicates. I have a separate customer entry form which works nicely and checks the following:


- it first looks at the first character of the first name and matching full name. If it finds a match, it will then open the form frmMatchingNames - which has a listbox listing all names where the first letter of the first name matches and the last name matches exactly (ie. similar names). The user can select a name on that form which will cancel adding and go to the name selected. Otherwise it checks for an exact match.

- if it finds an exact match, it asks the user if they want to continue adding the duplicated name. If they select No, it then deletes the current person being added and opens the form with the matching name.

See the example in the above to see how it works.

[bundy75]

Thanks but seems way to advanced for this cowboy

[pkstormy]

It's not really that advanced once you get familiar with the coding (give it a try to understand). I take it you're not much into vba coding and functions. This is something you may want to get the hang of for what you want to do regardless of how you do it. You can also possibly try to utilize the dlookup command to see if a name matches (minus the last few characters) but if you want to make it function/work ideally.....

Either way, you'll need to learn a little vba.

[pkstormy]

For an example utilizing the dlookup, see here: https://www.accessforums.net/code-re...mple-7545.html

That might be a little easier. If it finds a matching record using the dlookup, you can then return that record to the form.

[pkstormy]

Or better yet, just have a listbox on your form and set the criteria in the rowsource for the last name field = me.mylastnamefield.value

You would then requery this listbox in the afterupdate event of the mylastnamefield.

If a user clicks on a record in the listbox, you then (maybe canceling adding) and go to that record selected in the listbox.

I'll try to come up with an easy example and post it.

[pkstormy]

I reposted the example with a listbox showing matching names on the add customer form. You can create your data entry form, disregarding the duplicate finding method and can just use the example to see matching names in the listbox on the form after you enter the last name.

Hopefully that makes things a little easier. The listbox just has criteria which matches the lastname on the form. You could add criteria under the first name in the listbox for showing exact matches. Using this method, there's little/no coding. Just a listbox showing matches.

Ideally then, you could just add some code so that when they click on a name in the listbox, it goes to that record.

Again, I hope that helps. I kept it simple if you disregard the duplicate checking routine.

[bundy75]

Thanks for the help I've been trying to get in vb coding.

I'm trying to get the form to populate with a combo box. With no success,

The form record source is from "Add_Client_query", I have a combo box name "Surname_combo" which has Client_Id and Client_Surname when the user selects a surname I would love it to update the record.

Has seen another post that used Form.Recordset.findfirst but I've had no luck


Any suggestions

[bundy75]

Sorry also when you said you reposted listbox example where was that???

Thanks

[bundy75]

Hmmm just read something that suggests you can't use Recordset with ODBC . Bugga!!!!!!!!

[pkstormy]

You're using Recordsets with ODBC and you find the example posted complicated?

It's still a technique method and doesn't matter or have anything to do with ODBC but if you're at that skill level, the example should be easy.

I use this technique on recordsets which have ODBC Linked SQL Server tables. It's just a matter of opening a recordset (within a function) as the example illustrates. The code is ADO code versus DAO code.

It simply opens a recordset using the Like statement and tests to see if the recordset is empty (doesn't matter if it's ODBC or not). If not, then it opens a form showing matching records. I just put the routine in a function which is called after the lastname is updated. You could take all the code from the function and put it in the afterupdate event if the function worries you. I like ADO style coding much better than DAO coding but the method is still the same. I only encourage you to understand it because it does exactly what you specified.

See here for the example: https://www.accessforums.net/code-repository/customer-entry-find-duplicate-similar-names-7680.html

[bundy75]

Thanks for the vote of confidence even is it is unjustified, using recordset was something I tried from reading through this forum. Which I must say has been invaluable to me for this project. Anyways enough rambling ...


ADO vs DAO not sure what that is I go away and have a read up about that.

Keen to have another look at your example but couldn't find any download on your link??

Thanks

[pkstormy]

In regards to ADO versus DAO, use which one you find the easiest to understand. There really isn't a significant difference of one over the other. Some say DAO is a 'little' bit (milliseconds) faster while others say ADO is easier to use and understand.

DAO was the original way of coding and then came out ADO.

I personally like ADO. To me, it's easier to understand. It's really just a matter of slightly different syntax for some commands even though vba is for the most part, just the same.

Don't be afraid to leech off of others coding. That's the best way to learn. Each developer has their own technique on how they do things so take their examples and modify them to make them your own. Use any of mine (or others) examples and modify them to fit your needs.

If you're using SQL Server linked tables (or even MSAccess linked tables), you've made a good choice for setting things up. I personally have always liked using SQL Server linked tables. Once you get the hang of opening recordsets and writing functions, it's unlimited what you can do (just always, always remember to close your recordsets when you're done with them.)

You may also enjoy this: https://www.accessforums.net/sample-...ardy-7506.html

[bundy75]

Cool thanks,

I'm using mysql as the backend and ms access as a front end.

Your example looks exactly like what I want but am reluctant to put it in till I can understand it a bit better.

Do you have a more simple example, maybe a simple scenario of a combobox populating a form using Recordset?

My understanding is I need to take value from combobox and perform a select statement on the correct table. Then somehow use this statement to set record on current form.
Thinking

Dim strSQL As String
strSQL = "Select * from Clients where Client_Id = """ & combobox.column(0) & """
Form.Recordset.find strSQL
Form.refresh

is this heading in right direction??

Hey gunna start new thread with full description of my problem.
https://www.accessforums.net/access/...html#post29679

[pkstormy]

Here's something really basic that I threw together very quickly. See if this is more of what you're looking for.

The 2nd example uses the NotInTheList for a combobox.

If you're utilizing a combobox, you can take advantage of the combobox itself since it shows names already in the db. Otherwise, you have just a text box and do a strSQL match.

The 3rd example uses 1 name field and the strSQL to find a match.

Also see this post if you want to search for clients easily by alphabetical letter: https://www.accessforums.net/code-re...html#post29690 (I use this method all the time.)

And here's a couple of more examples for searching for clients/customers: https://www.accessforums.net/code-re...html#post29691 (these examples are more basic and you may find them easier.)