datasheet to form

[SDLevy]

Hi,

I am trying to set up a datasheet as the first entry point to the data base. I want the users to do a find in the datasheet and if they find the person, doubleclick on the record and have it open a form - already created with multiple pages and tabs.

I've tried setting up a macro on the detail line in dblclick, but that doesn't work.

Also, if the person does not exist I want it them to be able to add a new record using the forms.

What I am trying to do is prevent the new user from overwriting the first record in table.

Thank you,

Susan

 

[Jack Cowley]

I think I might approach this a little differently. You can create a combo box on the form you are trying to open. The user will type in or scroll down in the combo box looking for the person. When the person is found the data in the form moves to that person. You can do this using the Wizard by adding a combo box to the form. On the Wizards first screen select the third item, "Find a record..." finish the Wizard. Now starting to type in the persons name will take you to that person or it will get you close to the name you are seeking. If the person does not exist in the combo box you can use the Not In List event of the combo box to open the data entry form for people and go from there. You willl find how to use the Not In List event here.

If I have not made this clear enough just let me know and I will try to assist you further.

hth,
Jack

 

[SDLevy]

stillneed some help

Hello Jack,

Thanks for your suggestion. I did try it - first without the code attached. As you can imagine we had a bit of trouble.

Now I've found a spare minute to try the code. I am not all that familiar with doing code, but tried to follow the article that you suggested.

I am bogged down with how to put this into play with a single table. The example explains how to it using two tables and I can't translate this to my use.

This is what I've put in:

Private Sub Combo324_NotInList(NewData As String, Response As String)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
'Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
'Ask the user if he or she wishes to add a new person.
Msg = "" & NewData & "'is not in the list" & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
'if the user chose Yes, start the form in data entry mode
DoCmd.OpenForm "frmContributionEntry", , , , acAdd, acDialog, NewData
End If
End Sub

What happens is I get an error message saying this person is not part of the list.

Any help would be greatly appreciated.

thanks

Susan

 

[Jack Cowley]

Susan -

Your code is incomplete, but let me ask you this - Does your MsgBox open saying that the name does not exist and do you want to add it? Does your form "frmContributionEntry" open if you click the Yes button in your MsgBox? When do you get the error, before the form opens or after? Do you have code in the forms ("frmContributionEntry") On Load event?

Lots of questions and no solutions just yet, but you appear to be on the right track!

Jack

 

[SDLevy]

Good Morning,

> Does your MsgBox open saying that the name does not exist and do you want to add it?

Yes, it does do this.

And when I click on Yes. I get the error that the name does not exist on the list and to choose from the list.

The first part of the article you recommended said to change the property of Restrict to list to Yes. So it makes sense that I am getting this error. But if I turn it off it just jumps to the first record in file - which is of course what it did before I started mucking about with code.

>> Does your form "frmContributionEntry" open if you click the Yes button in your MsgBox?

Nope - this is the part I couldn't figure out from the article.

>> When do you get the error, before the form opens or after? Do you have code in the forms ("frmContributionEntry") On Load event?

The error is when I click on Yes in "add record" question". And the only other place that I have code is in this unbound control to use a query that provides the concatenated names.

I'll keep trying different things, but any other suggestions would be greatly appreciated.

Susan

 

[Jack Cowley]

Susan -

If your form "frmContributionEntry" opens then take a look at the code in the On Load event of that form and compare it to the code in the article. If that is working correctly then the form should open with whatever data you are passing it in the variable 'NewData' and that information should be added to the proper control in the form.

As I mentioned earlier there is code missing from the the code you posted. When you close the form "frmContributionEntry" the code returns to your posted code and does some more checking, etc. so be sure you add the additional code.

If you still cannot get it to work you can zip and attach your db here and I will take a look at it for you.

Jack