Access & Charity DB

[skk391]

Hi all,

I have created a DB for a charity. All seems well, I have created the tables and forms etc. Create a few queries so that users can find out outstanding pledges etc.

I have become stuck on a requirement. I have a drop down box on a field in my form for 'event' I.e on what event was a donation given. I have pre-populated this list i.e sports day 2012, charity walk 2012, charity run 2012 etc. It doesnt reference a list I have just created a drop down box.

How can I configure the field so that if there is a new event the user can enter it in and then use it.

I know it is possible but am unsure how to configure it.

I am using Access 2000, but can upgrade to 2003 or 2007 it needs be.

Many thanks all.

 

[Triacona]

Dear Skk,

I would first use a table called something like tblEvents.

Then populate it with the fields you want and the data.

Then in the drop down box properties in the Data tab, choose the Row Source type as Table/Query.

Then goto the Row Source and choose your table tblEvents

Then goto Control source and choose the field you wish.

Then in the table create a New data, so if the user clicks on the drop down arrow, he will see New in the list.

Then create a form, i.e. NewEvent, use the tblEvents as its source.

Then in the Properties of the Drop down box click on Events Tab and in the After Update field click the ... button and choose Code Builder.

This will take you to the code screen.
Insert the code below.

'-*-Combo Dropdown
Private Sub cmb[i]YourComboBoxNameHere[/i]_AfterUpdate()


 Dim stDocName As String
        stDocName = "NewEvent"
    Dim stLinkCriteria As String

    If cmb[i]YourComboBoxNameHere[/i] = "New" Then
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        DoCmd.GoToRecord , , acNewRec 
    End If
    
    
End Sub

You can also add a refresh command in the code so that when the user closes the form the Combo box refreshes.

I hope this helps!


Thank you,

Kind regards

Triacona

 

[Triacona]

Oh on the Form NewEvent you can add a close button with the refresh code.

Private Sub CloseWindow_Click()
On Error GoTo Err_CloseWindow_Click


    DoCmd.Close
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_CloseWindow_Click:
    Exit Sub

Err_CloseWindow_Click:
    MsgBox Err.Description
    Resume Exit_CloseWindow_Click
    
End Sub

Thank you,

Kind regards

Triacona

 

[skk391]

Hi,

Thank you very much for the information. I have had a quick look at doing this, but struggled a little. I not that fimilar with Access. I'll try again at home later today. If there is any other nuggets of information that you could share please feel free to add it. I'll see how I'll get on with it....

 

[skk391]

Hi,

I have created a table to hold my events. And create the drop down list on my donation table, pointed the values to the events table and when I open the donation form I have a drop down box appearing with all the events within my events table. However when I select a value i.e sports day and then move the to next record, the sports day entry isn't written to the database. All other vales i.e name address and email address are but nothing that I have pre-selected from my drop down box.
Any ideas?

 

[Triacona]

Hi Skk,

That is a strange error...
Is your control source for the properties of the drop down box set to the Event field you want updated?

This will enable Access to populate the table from the choice of dropdown on the form.

I hope that helps...

Thank you,

Kind regards

Triacona

 

[Triacona]

Also make sure your form has the dataset for the table, i.e. the field you want to populate is choosable from the dropdown in the control source.



Thank you,

Kind regards

Triacona