Simple Find/Add/Edit form???

[stildawn]

Hi All

I am totally new with Access, I have experience with Excel/Outlook/Word in regards to VBA, as well as VBS etc.

I am making a move to Access for handling a ever increasingly massive job list database that I currently have in Excel.

Anyway using the import from excel tool the data is now all in Access and I've messed around with it a bit so that it looks good.

My issue is that I'm trying to make a simple Find/Add/Edit form but the form features in Access are rather confusing at least to me. I would do this in VBA userforms in excel, but I have no idea how to get it working in Access...

So basically could someone guide me through making this form? What it needs to do is:

- Able to search for jobs (each row in the main table) and display the results
- Users need to be able to edit the fields (from the Find above) and then save the changes
- Users need to be able to create a new job (row in the main table) by filling in the fields and saving

Is this possible? How on earth do you do it in Access?


Also the users so far don't have access installed, I have heard of the the Access Runtime tool (or something like that) that Microsoft has for working with Access databases, are forms able to be accessed through this tool? All the users need to do is access this one tool. To add/edit jobs...



Thanks in advance for your help.

Cheers

[ItsMe]

If you develop the application correctly, it will function just fine in a runtime environment.
http://www.microsoft.com/en-us/downl....aspx?id=10910

All you have to do is download the RT exe and install it on the client. This will give them the ability to open accdb and mdb files. I found it nice to disable the ribbon and all toolbars too. Then I just make sure I build the correct controls for the User to navigate edit etc.

It is not everything you need, but you can check out this sample I uploaded here.
https://www.accessforums.net/sample-...orm-38074.html

In it you will see an example of a form that the user can use in RT. If you disable the Access toolbar in your app. you will need to ad a search capability in a shortcut menu or the users can use CTRL + F

I also uploaded an example of using combo boxes. In it you might get ideas on how to search for and change a form's recordsource. It shows dependent cascading comboboxes.
https://www.accessforums.net/sample-...xes-38171.html

Neither of the sample DB's I listed are heavily noted. The idea being that people look at them to get ideas on how to approach certain problems. For example, the comboboxes may depended on a table and have its properties set to a table via the form's properties window. While another combobox gets it rowsource from SQL in the VBA module.

[stildawn]

Thanks ItsMe

This: https://www.accessforums.net/sample-d...orm-38074.html

Looks perfect, its almost exactly what I want, except it doesnt search for a record.

I know its a big ask, but could you please run through the steps on how to get a database to open like that using runtime? I'll keep trying with the forms, but will probably need help on those also, I assume I can open up your example in full access and have a look (will have to do later when I'm at work) and have a look at how you made those forms. Man that is just super close to what I want it actually looks more complicated than what I need as I just need it to open one form.

[ItsMe]

If you want to search for a record all you need to do is set focus in a field and hit ctrl + f. You can also use the search functions built into Access menu items.

To get any DB to work in RT all you need is to have a form open at startup that allows the user to navigate through the DB. I believe the startup settings are already adjusted in the example. RT does not have any access to queries and tables. So you need to use forms for EVERY user input/interaction. Reports will open too. You just need a form to tell the report to open.

The example DB should be opened using a full version of Access so you can look at it and see how it ticks. Although it is a simple DB with only a few objects, it does have a lot of code examples. The examples do a good job of demonstrating how to control a subform from a Main form. The other sample is a good next step because it can help you search and filter out results from different tables.

When you get to work take a look and post back with any questions. It may or may not be what you need. My view on applications is you want to be able to control the User's actions. This is why I like this example. It employs a technique I use often. The user does not have to open a bunch of different forms to switch from edit, to add, to viewing records without fear of accidental edits.

It looks simple to the user, but there are a couple things going on behind the scenes.

[stildawn]

Would there be any way to do this: "If you want to search for a record all you need to do is set focus in a field and hit ctrl + f. You can also use the search functions built into Access menu items."

Inside the form? The people here need it as simple as possibe.

I know its different, but in excel I would simply have a textbox on a user form and then a command button next to it that would simply loop through all the rows in the worksheet and when it matched the value entered in the textbox it would stop and bring up that lines data.

I'll have a play tonight and tomorrow and see what I can figure out on my own. Its quite different to excel that I'm used to, I managed to make a massive form which had text fields for every column, but it wouldn't let me edit any of them or search.

[Missinglinq]

The simplest way to handle the search part is to use a Combobox; the Wizard will take you through the process. If you haven't already done so, create a Form based on your Table, including all the Fields you want displayed.

Then simply:

• Add a Combobox to your Form.
• The Combobox Wizard will pop up
• Select "Find a record based on the value I selected in my combobox."
• From the Table the Form is based on, click on the Field you're searching by (a Field that is unique for each Record) to move it to the right side.
• Hit Next.
• Size the column appropriately.
• Hit Next.
• Name the Combobox something appropriate.
• Hit Finish.
  
  

Now you can drop the Combobox down and scroll down to the item to search by, or you can start to enter the item by typing and the Combobox will "autofill" as you type. Hit <Enter> and the Record will be retrieved.

Be warned, although much of Access VBA and Excel VBA is the same, their are differences! Some Functions are present in one app but not in the other, and some Functions, with identical names, do not do the same thing in both apps!

Linq ;0)>

[ItsMe]

The example DB will absolutely allow you to search. Even if you disable the menu bars/ribbons in Access RT, you can use the keyboard shortcuts. I even have a custom right click menu you can import into your own DB that will work with Ribbons disabled.

The trick to the whole thing is getting your tables normalized in a way that allows you to build queries. You will quickly discover queries are not always updatable. If your data structure is not well planned and thoughtfully assembled, little of it will work well.


So, if you are having trouble building a query of a couple of your tables that allows you to add records, you will need to take a couple steps back and readjust things.

It is a common mistake made when importing a bunch of spreadsheets to Access to not place the data into the appropriate structure (relations).

[stildawn]

Thanks Linq

That sounds good enough, I'll see if I can figure that out when I have full access to access (haha).

Hopefully I can figure out how to add and "Allow Editing" button to unlock the fields of the searched record. And a ADD button that will clear all fields and allow the user to enter a new record.

[stildawn]

Hi All

This has sort of moved from the original line of questioning.

I have basically figured out all my problems myself, I now have a database that opens and runs through forms in Runtime, everything looks and works great as far as I can tell..

Before going live, do I need to do anything special to set this up for better multi use? Basically we will have like 10 people who will be editing records, potentially all at the same time (ish) they are in teams so it shouldn't conflict that much, but can someone tell me how exactly access handles this?

Thanks

[ItsMe]

If two users are on the same record at the same time, the user may see a warning that the record has changed and ask the user if they want to save their changes or abandon.

[stildawn]

That sounds like what I would want it to do anyway. Good thanks, so everything seems sorted.

[ItsMe]

If you have multiple users on the same file you can strategically place a line of code in the afterupdate events of certain controls/fields. It probably won't be an issue but...

If Me.Dirty = True Then Me.Dirty = False

Placing this in the afterupdate event of a textfield will save the record.

[stildawn]

And what does that do? Save it? I don't think people will be editing the same record at once.

Though whats the definition of the same record at once? Like I have a form which just displays a query in a datasheet view... This will be displayed all day haha. If a user makes an edit on one of these records, is that counted as two users at the same time?


Also just one random question... I have a form which has a listbox for the search feature... I need to make many duplicates of this exact form and layout, the only thing changing is the query the form is generated off. At the moment I am making each one from scratch, by clicking on the query, then form. Which brings up a form will all the fields listed, I then move them all around and add the searching listbox.

Is there a way to somehow save this template? But so that it still builds off the correct query?

Thanks

[ItsMe]

And what does that do? Save it?
Thanks

Yes, and I meant to say textbox. I don't have a lot of experience with getting that msg/warning. For me, it might happen when I open a popup form to get additional info from the user and then run an UPDATE query. I use the save command to avoid the warning.

Not sure I totally follow your other question. You can duplicate objects like forms and queries by right click copy and then paste. You do all of this in the main navigation pane on the right, where all of the objects are listed.

[stildawn]

Um

Like this is what I have done so far:

-Made a query from main table (I only have one actual table of records its a simple small database). This query just takes the entire main table and filters out by two fields. Saved this Query called "EXP AFR"

-Then on "EXP AFR" query, I hit "Form" from the ribbon. This automatically made a form with all the fields from the query, on this form you can only search by ctrl + f. I then moved around the fields and made a big listbox using the wizard which "Finds based on selected". So now there is this form with all the fields, and a listbox populated from the Query "EXP AFR"

So my question is, I want to do the exact same thing above, but on a different query (this time filtering out two other fields). But the layout, listbox etc needs to be exactly the same as the first form I created manually. But the listbox will display records from this new query, and it will populate the fields etc like before.

I need to do this probably 10 or so times, the form will look exactly the same but will be "based" on a different query each time?

Does that make sense haha, its hard to explain.

I could just send you my database as it is right now which would be easy to see.

Cheers

EDIT: Another random side question haha, on my forms there is a ADD button. If two users were to "ADD" at the same time? Would this cause any issues? I know its highly unlikely but how does access handle the creation of new records (and hence ID numbers).

Thanks