Access Database Help please

[josh.clare]

Hi all,

i have created a very basic access database that contains all the information i need for our jobs at work. a majority of the data is in 1 table.

All i need to do now is create some userfriendly forms to add/edit data in the tables.

i have uploaded my basic database here:
http://81.134.132.58/josh/Database7a1.zip

Can someone please help me in creating these forms, as you can see i have made several attempts but am struggling. i want to create forms for sections of the table if possible,

Thanks,
Josh

 

[Trevor G]

Use the Forms Wizard to help create the forms based on the tables, then add some command buttons for finding, filtering, add new etc the command button wizard can be used here.

 

[josh.clare]

Hey Trevor, i can use the form wizards to get the very basic forms, but i would like to add some kind of search/filter option onto the forms so that if they want to edit a specific row of data, they can search/filter to find the data they wish to edit, could you please help me insert this,

thanks, josh

 

[Trevor G]

What you need to add is a combo box which will list the companies or what ever then click and find based on the value you selected.

Open the form in design view then add the combo box and the wizard will start then make sure you have it selected to find based on a record in the form, that should get you going. Your users can either select the drop down or start to type in the company name or what ever you decide to use.

 

[josh.clare]

that works fine but the company is not the primary key and there are duplicate values so is there a more advanced way of filtering/searching i can add to my form?

thanks
josh

 

[Trevor G]

You can group the companies so you only see 1 then the form can filter to all of the records and you just navigate accordingly.

Open the form in design view, select the combo and open the properties, then go down to the Row source and click the elipsees button on the right then in the query window make sure the companies field is shown and then look to use the Totals icon and it will group them together, then if needed sort them and make sure there are no empty records so add <>"" in the criteria, save and close then look at what happens on the form when you change the view.

 

[josh.clare]

hey trevor,

i think i am getting there slowly,
could you please take a look at what i have done so far, i have created a form and then i have inserted a combo box and done what you said but i dont think it did anything.
http://81.134.132.58/josh/Database7a1_2.zip

much appreciated,

 

[Trevor G]

Josh,

The form works fine in the properties you have the combo also selecting the reference, in this case perhaps you would show both columns so the user would then have an idea which one they want.

The combo finds the record, it doesn't apply a filter.

 

[josh.clare]

yes but when i remove the reference, the combo box stops working :s i am confused.

 

[Trevor G]

The combo box has an after update event which is looking at the reference field if you change the code to the company field it will work.

Private Sub Combo88_AfterUpdate()
On Error GoTo Combo88_AfterUpdate_Err
DoCmd.SearchForRecord , "", acFirst, "[company] = " & "'" & Screen.ActiveControl & "'"
DoCmd.RunCommand acCmdApplyFilterSort
Combo88_AfterUpdate_Exit:
Exit Sub
Combo88_AfterUpdate_Err:
MsgBox Error$
Resume Combo88_AfterUpdate_Exit
End Sub