Access 2007 form questions

[Dunnopat]

Hi I made a form. It is used to show old data and add new. By default, it shows an empty/new record. What I want it to do is that when the user types something in a combo box (Social Security Number) and the data already exists, the form will show the record that has the same SSN. If the SSN doesn't exist, then it will just start filling the new record. I was thinking that the combo box or text box would point to no field, search if the typed text is found from records, then either go to that record or add the value into another box that is connected to the table. But how do I do that? Does anyone have any better ideas? I feel like my head is going to explode. Another thing: One form shows a list of things. There is a calculated fields MonthsLeft and TimesLeft (number of times). Whenever the TimesLeft in a record is 1 or MonthsLeft is 1 or below, it has to show the user a message/reminder. Reminded field in the underlying table will become true. However, if the user clicks "Remind me later", then he will be reminded again when he opens the form (reminded will not be set true). Note: I haven't written any VBA code. I only used macros and stuff like that. I was just asked to create a small database because I am the one who uses computers more than anyone else here. The database is working well, looks nice and just needs these few things to make it "perfect". I am not at work right now and I can't view/write this at work but I'll see if I can get more information if needed.

 

[Trevor G]

Welcome to the forum,

If you place a combobox on the form which has all the records you can use the wizard to find a record on the form, that way only the list of records will be found in the combo box.

If you want to be able to add a new record because the search doesn't find a record so you type in the SSN then you can use an event called not in list, where you will need some code to add the record.

The following code is an example you would have to change the table name and field name, I have highlighted them in red for you. You also should check the properties of the combo box to ensure the limit to list is set to yes

Private Sub Company_NotInList(NewData As String, Response As Integer)
Dim lngresp As Long
Dim Db As Database
Dim rcd As Recordset
lngresp = MsgBox("Do you want to add this to the form?", vbYesNo + vbQuestion, "Add to List")
If lngresp = vbYes Then
Set Db = CurrentDb
Set rcd = Db.OpenRecordset("tblCompany")
rcd.AddNew
rcd![Company].Value = Me.Company.Text
rcd.Update
Response = acDataErrAdded
rcd.Close
Else
Response = acDataErrDisplay
End If
End Sub

 

[Dunnopat]

I couldn't get it to work. Does it matter that the SSN field contains text?

I'm having hard time because access I use is in other language.

I right clicked the box and added modified code to it. Nothing happens.

After setting limit to list to yes, it just says that the value is not found.

 

[Trevor G]

Have a look at the attached database, I have placed a find button on the right fairly large, it will take you to a form and on the top right there is a combo box, if you select something from there it will take you to the record on the form. You can look behind the button and search for the event and look at the code and replicate it in your database.

See if you can get that to work first, then post a reply and we can then look at the next option about a new record.

 

[Dunnopat]

Hello.

Thanks for trying to help. I have the same problem with my db that I have with that one.

Searching and going to the found record works. When it's not found.... thats when the problems come. It adds the new SSN. However, it goes to a new record, while the form stays in the empty one. If I then type names and other info and then look at the table:

ID SSN NAME
1__abcd
2______some name

I changed the code so instead of adding a new record, it adds the value into another field that is bound to the SSN in the query/table. It works but it always gives the error that it is not found from the list. After clicking ok, I can type other data. Any way to remove that error?

Edit: Why does my first post look so ugly.

 

[Trevor G]

Can you strip down a version of your database and attach to this thread.

Sometimes when folk write a thread they place it all in a single paragraph so people start to read it then get lost in the whole contents.

Its best to have a brief explanation then list the points, and objectives on separate lines, like MS Word numbering.

Have you read the guidance threads?

 

[Dunnopat]

Ah, I finally got to respond.

I finally got it to work. I had to modify it a bit. It's not exactly pretty but it works and the user won't notice anything weird.

Thanks a lot for help. It is all thanks to you that I got it to work. Anyway, I must have been very tired when I posted that first post.

 

[Trevor G]

Well done, not all things are easy in Access.