Combo box Not In List / new record creation

[Rramjet]

Hi - I am a novice to Access and VBa - so please bear with me...

I have a combo box that lists ID numbers. The user types in an ID - and if it exists - the form reveals the relevent information associated with that ID.

However, if the ID does not exist, then an error message is generated (Not in List) and one cannot proceed until a valid ID is selected.

Unfortunately this means that one cannot then create a new record (or add the new ID to the list).

Searching around I thought I had found a general way around it - as follows:

Private Sub Research_Id_NotInList(NewData As String, Response As Integer)

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."

Else

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("TABLE_1", dbOpenDynaset)

Rs.AddNew
Rs![ID] = NewData
Rs.Update
Response = acDataErrAdded

End If

End Sub



Unfortunately, although it works (sort of...), it creates anomalies in the database. This is because the ID combo box is in a form header while I have a subform in the body (listing the instances and dates of instances for that particular client). ..so when I click OK, it creates a new record (fine and dandy) ... but ALSO changes the ID of the client whose information was just being displayed to the ID I just typed... (oooops)

So, to prevent that, I have tried to write into the code

DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acNext

..to get to that "(new)" record and to make sure all the information fields are blank before it starts, but no matter where i put that - all that does is break it...

BUT, IF I can get to that (last) blank record - the one that Access labels as "(new)" - then I can make the NotInLIst code work (...almost...) - I say almost because if I manually go to that last "(new)" record and then type in an ID that is not in the list (and the code initiates and I click OK) - then I find it has created TWO new records, one as I typed the ID in - and one according to the VB code!

...If I could then delete one of those records (from within the code - ignoring the error messages so it just deletes), then all would be fine (well, except that in the original code I would still have to find a way to GET to that last "(new)" record...

...and find a way to prevent users from clicking YES originally...

..and ...oh my ...So I have been running incircles all day...

so there must be a better way - (I hope)... and therefore with hat in hand I come to the forum... any help please ?

[June7]

ID is NOT an autonumber type? The combobox is UNBOUND?

Review https://www.accessforums.net/forms/q...54/index2.html
You might skip down to post 26.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Rramjet]

Thanks for your reply June 7! Appreciate it.

No ID is not an autonumber...

Not sure what "unbound" means in this context ... it was a combo field dragged into the header (and thus initially it displayed "unbound") before it was associated with the form's underlying table's ID field.

I Have attached a copy of the database I am tryin g to create. I like the functionality but it makes it difficult for me to see how to add a new record (which - very importantly - and notably I suppose - will never be a client that is not contained within the CLIENT table... which is regularly updated from an external source...)

I am currently trying different methods... like using the menu to go straight to a "(new)" record - but I wish I could restrict it to dsiplaying just that single instance... I'll keep looking... there must be a way... (but I am very new to all this...)

[Rramjet]

...and (sorry I didn't mention) yes I have looked at post 26

Private Sub Vehicle_ID_ComboBox_NotInList(newdata As String, Response As Integer)
If MsgBox("Do you want to add this vehicle?", vbYesNo, "Add vehicle?") = vbNo Then
Response = acDataErrContinue
DoCmd.GoToControl "Vehicle_ID_ComboBox"
Me.Vehicle_ID_ComboBox = Null
Else
DoCmd.OpenForm "Vehicle_Add_Frm", , , , acFormAdd, acDialog, newdata
Response = acDataErrAdded
End If
exit_it:
End Sub

but I don't want to set to Null... the trouble is I cannot visualise what I want to do logically (if I could, then I would find the codeto do it) and I suppose I cannot do that because I don't quite get the relationships I have set up... I know it works for what I neewd to this point, but not for creating a new record in the particular table of interest...

[Rramjet]

I have tried to clarify the logic of what it is that I really want to do...

I have an ID that is not in the list in the Research_Id combo box.

(because it is not on the list) I want to use that ID in a new record.

If possible I wnat to copy that ID to the clipboard (or to a temporary field), then return the combo box to the original legitimate ID for the particular record that has the focus (the one that was there before I began typing the "new" ID), then go to a new blank record, then paste the new ID into the combo box, then display a message (You have created a new record for ...ID. Do you want to continue with data entry, or do you want to discard this new record?). If yes, shift the focus to the first data entry field, If no, delete the new record...

Obviously I will have to bypass the "Not in List" error (Response = acDataErrContinue),
BUT if I do that, then that "new" ID (the ID that was not on the list) overwrites the legitimate ID (Research_Id that was there before I began typing the "new" ID in) for whatever client had the focus at the time of the procedure.

Hope that helps...

[t. hagan]

One of the tricky parts of working with forms in Access is that the form will jump around as you add new records. You may want to look into using a recordset clone with a bookmark to mark your place in the table and then return to it.
t. hagan
www.peachtek.net

[June7]

A control is UNBOUND if it does not reference a field in ControlSource property.

I can't find the problem code and so don't know what form you are having issues with.

Data structure does not appear normalized. It is a balancing act between normalization and ease of data entry/output. This arrangement might suit your needs or it might cause you real headaches down the road.

[Rramjet]

Hmmm - perhaps I have not explained myself clearly enough.

I want to be able to create a new record in all the forms that can be accessed from the menu...

My problem is that if I try to create a new record in any of those forms with an ID that is "Not in List", I run into the "Not in List" error - meaning that I cannot create a new record for any ID that is NOT on the list.

I can bypass that error with the code that I first posted (and so get a new ID into the list) BUT that causes BIG problems. First because it overwrites the ID of the record that had the focus. Second because it creates a new record in that record. Thrid it creates a legitimate new record (this latter is what I wnat - the two former are NOt good).

Now ...I COULD just allow the "Not in List" error message to operate and so force the user say okay, selct an ID that IS in the list and then to manually navigate to a new blank record. Fine - but then, once there, the "Not in List" error is STILL operative....

...and that's a problem. So perhaps I could create a dedicated "New record" form, where I could add an ID (it would be the only field on the form) and I could navigate there by a "New record" button, add the new ID and then navigate back to do the data entry... butthat seems a bit clunky... I am sure there must be a solution to my problem, but I am so very naive to Access and VB code that I just cannot see where I am going wrong...

[Rramjet]

Thnks t. hagan - even though I have absolutely NO idea what you just said, it sounds interesting - I'll look into it

[Rramjet]

... and I believe the data is normalised ... there is NO data in any table that is duplicated in any other table (except for one instance of firstname and lastname in just ONE table outside the CLIENT table (which was an inherited feature I had not got around to deleting yet...). ...if that is what you mean by normalisation of course...

[Rramjet]

Okay - I think I have (tentatively) resolved the issue. If I create a new form with just the ID field on it (linked to the underlying form table), then in the primary form I make a new record button and use the OnClick Macro to go to the form's ID control, then go to a new record, then close the form, then open all the related forms in order and set each of them in sequence to "(new)" record (and close them all on the way through) and finally open the "New Record" form (for that table/form) and go to the ID control. Once there the user will find a message above the field (control - NOT a combo) asking them to enter the ID they want to add a record for and click DONE when they are finished (Interestingly the form will only allow an ID that exists in the CLIENT table - have no idea why - but it is HUGE BONUS), then ...bear with me ... the DONE button closes that form and re-opens the initial primary form. NOW - all I have to do is make sure (somehowe I have not worked out) that when I reopen the primary form the focus is on the newly created record, then everything will be fine and dandy... I hope ...not fully tested yet ...but I think you get the idea... VERY clunky, but as I know nothing about Access ot VB I can only be guided by the logic of what need to be done (I am sure that somewhere there will be a VB shortcut to do all I have just done, but *shrugs* if what I have works ...*opens hands palms up*...

[June7]

First indicator for me that the data structure might not be normalized was that there are sooooo many fields in each table. And then I thought not every field would have data for every record. After another look, I get a somewhat different impression. Whether or not this structure is optimal could depend on what you plan to do with data, what analyses, if any, you need to do.

I am not clear about the data relationships. Will only one record in each research data table (the subforms) be associated with a client record (the main form)? If yes, why show the subforms in Datasheet view?

Will every client always have a related record in each of the research data tables?

I think some of the issues you are experiencing might be caused by form design. For example BITSEA RecordSource is a query with INNER JOIN of BITSEA and Client tables. INNER JOIN requires records in both tables. What is purpose of the Research_ID combobox? If it is for entering filter criteria then it should not be bound. Then there is a subform that is also bound to BITSEA table but doesn't allow edits. I find this all very confusing.

[Rramjet]

Hi June7. First, I need all those fields as (for example the TSCYC contains 90 core items - which cannot be split and then there is all the peripheral information that goes with those fields) - and that is precisely where I ran into the first Access bug - It is extremely limited in the amount of field information (no. of fields and/or fields x parameters of those fields) it can contain in one table (more limited than the official design specs would lead you to believe) - thus it is not a true database (like FileMaker for example) in that sense... but I must work for my masters who seem to like Access - for their own reasons I am sure...

For each client there can be multiple records in each of the subforms - each of which represents an outcome measure, which of course can be repeated many times (every 6mths in my circumstance - until a case is closed - which could be many years.).

I plan to do no analysis at all. Databases are not analytical tools, nor should they try to be. I export to Excel then import into SPSS (FileMaker can export directly to SPSS format - when is Access going to catch up?)

Some clients (unfortunately) will never have a related record - that is they will never complete an outcome measure of whatever variety... there are many legitimate resons for this, but the bottom line is, we still must list them in the CLIENT table.

The way I have relate4d the tables, queries and forms means that I can use the forms utilising a tabbed structure to display BOTH summary information (for example all the dates of multiple instances) in one tab AND have the individual record information (for individual filed data entry) AND have the field (scale and subscale) totals displayed on a third tab - all within the one form.

All that of course then causes the "Not in List" error problems I experience when I want to create a new record - the problem I have come to this forum seeking a resolution for - but I think I have found a workaround (as described above) anyway. I'll continue testing , but the basic methodology seems sound at the moment. I'll let you know how I go.

As I say - I am a complete novice. I opened Access for the first time in my life just two weeks ago. I have never used VB code and just had no idea what Access even did. I inherited a MESS of a database - so I cleaned up the principle tables and dumped them into a new database and started building from there. I am positive I could have done a better job if I knew what I was doing (LOL) - but since I do not know what I am doing - I just think of what I need to do, try to make it a logical step by step procedure, then Google it, and mostly I can find some sort of clue to a solution - never the solution, just clues because mostly the forum code written to help others is just HOPELESS - just snippits with no clue about how to utilise it contextually...

Anyway...do you, with your obvious experience, have any suggestion for me at this point?

[June7]

Yes, Access is very limited when it comes to doing analyses but nevertheless it can do some (count, average, sum, deviation) and does have a graphing functionality (at least it does before Access 2013).

You actually seem to be quite capable at relational db design and have knowledge I don't. I never used FileMaker and no idea what SPSS is. Access is the only database software I've worked with (ignorance is bliss?).

So you don't want editing accomplished in the subform on the BITSEA form? This is only to display data? Could use a listbox for that. At least change the Parent_or_Childcare_Provider combobox to a textbox.

You did not respond to the question about the Research_ID combobox. If users make a selection in this combobox as filter criteria, they will change the data in the record. If you want a control for selecting Research_ID in a new record, then it cannot also serve as input for filter criteria. Need two controls for those different functions.

Order By property shows [_BITSEA].[Last Name] - why the underscore, why not just [Last Name]?

Suggest the RecordSource for the main form would be better not to be INNER JOIN. Try: FROM CLIENT RIGHT JOIN BITSEA ON

The BITSEA TOTALS tab shows images of data analysis reports. Is this a development goal? Manipulation of data is really what reports are for, not forms.

[Rramjet]

understanding might be slightly short of full... I now understand the need for two controls. The control on the form will be used to search and filter and I will exit the form entirely to add a new record (via the method described above).

Why the underscore? Good question - I had noticed it, but only in passing on the periphery of conciousness, but now you have brought it to my attention I will look closer.

An INNER join not the best way? Hmmm, this is where my understanding fails me ... I'll try your suggestion and see what what happens...

Development goal? Sort of. I merely want some scale (and subscale) totals to display - if it was my personal preference this tab would not exist - but a minor compromise on my behalf

Thank you for your suggestions June7. I appreciate your interest. I'll try your suggestions out and update you.

Oh ...I do have another question, but I might make a new thread ...It concerns, using macros, being able to copy the content of the Research_id field in one form, then in another form, perform a search and find for that record...

Thanks again June7