coding problem

S

sarahdaws

Registered User.
Local time
Today, 03:45
Joined
Sep 1, 2003
Messages
15
Hi Everyone
I'm struggling with this code to add an item to a table which feeds a combo list
The underlying table is called Addq, the current form is called Questionentryform

It seems to add the number to the values in the addq table (but I cant tell when) because if you click add a second time it then tells you that duplicates are not allowed.

The pop up asks me if i want to add the value then asks me over again without adding the value to the combo list.

I've tried refreshing, requery, setting the keyword values to nothing etc. Please help I've been scouring code for help for the past 4 days

Thanks
Sarah
P.s. I'm a novice at code writing so please make the explination simple, the xtra at the end of the coding is stuff i've already tried

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

'Adds a question number that is not in the list to the list, if the user wishes to do so.

Dim strMessage As String
Dim intAnswer As Integer

strMessage = "'" & [NewData] & "' is currently not in your list. Do you wish to add it?"

intAnswer = MsgBox(strMessage, vbOKCancel + vbQuestion)

If intAnswer = 1 Then

Set dbsVBA = CurrentDb

Set rstKeyWord = dbsVBA.OpenRecordset("Addq")
With rstKeyWord

.AddNew

!QuestionNumber = [NewData]

.Update

End With

'rstKeyWord.Close

'Refresh.QuestionNumberlist
'Me.Refresh
'Refresh

'Set rstKeyWord = Nothing

'Me.QuestionNumberlist.Requery
'DoCmd.Requery
Response = acDataErrAdded

Response = acDataErrDisplay

End If
End Sub
 
Code: 
Private Sub QuestionNumber_NotInList(NewData As String, Response As Integer)

    Dim db As DAO.Database, rs As DAO.Recordset

    intAnswer = MsgBox(strMessage, vbQuestion + vbYesNo)

    If MsgBox("'" & NewData & "' is currently not in your list." & vbCrLf & vbCrLf & _
        "Do you wish to add it?", vbQuestion + vbYesNo) = vbYes Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Addq")
        With rs
            .AddNew
            .Fields("QuestionNumber") = NewData
            .Update
            .Close
        End With
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub
 
its saying
'User defined type not defined' on the Dim line

(and i've already tried changing the references and its not highlighted for change - on the Tools options)

Sarah
 
You cant change the references while in debug mode... Stop the code first.

You ARE missing the DAO reference (eg you havent activated it)

Regards

The Mailman
 
Thanks I've done that

it appears to be adding to the drop down list and underlying table now but when I try to move to the next record error message 'The field 'Questionentryform.QuestionNumber' cannot contain a Null value because the Required Property for this field is set to true. Enter a value in this field'

This doesnt make sense cos there IS a number in the field

Sarah
 
Check your table design for your field. You will find requered set to true, change it to false....

Or if it truly is required, try adding a line
.Fields("Yourfield") = 0 'or some other "real" value


Regards
 
where do i add that line of code cos I really need it to be set to true
sarah
 
look at the line in BOLD

Code: 
Private Sub QuestionNumber_NotInList(NewData As String, Response As Integer)

    Dim db As DAO.Database, rs As DAO.Recordset

    intAnswer = MsgBox(strMessage, vbQuestion + vbYesNo)

    If MsgBox("'" & NewData & "' is currently not in your list." & vbCrLf & vbCrLf & _
        "Do you wish to add it?", vbQuestion + vbYesNo) = vbYes Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Addq")
        With rs
            .AddNew
            .Fields("QuestionNumber") = NewData
            [b].Fields("Yourfield") = 0[/b] 
            .Update
            .Close
        End With
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub
 
I've put this in but what goes in the 'your field' part??

sorry I seem to be a bit dim dont I

Sarah
 
Yourfield should be the field that cannot contain the null value

Regards
 
Can you post an Access 97 example of what you have?

Just the for, the table with all sensitive data removed, and any queries or modules that are necessary.
 
namliam said:
Yourfield should be the field that cannot contain the null value
Click to expand...

She said that the field that can't contain a null value is QuestionNumber which should already have been assigned with NewData.
 
I've tried that but it doesnt seem to like it

Now its saying item not found in this collection

Sarah
 
Then you would be passing an empty string?

Regards
 
After deleting the line we added earlier I cannot seem to replicate "required" error

Also i see that in this table Questionnumber is not required. Try deleting the extra line and trying again.

Regards
 
sarahdaws said:
i dont have 97 version only 2000
Click to expand...

Since Mailman does have Access 2000 and can help with your problem; I'll leave him to it.

For future reference though, when posting a database it is better to post a version in as many versions as possible as the scope for help can greatly increase.

Access 2000 allows you to save your database in Access 97 format. I think its under Tools -> Convert
 
You must log in or register to reply here.

Similar threads

C
Solved NotInList error
Replies
18
Views
1,356
ClaraBarton
C
P
number lock keeps going off
Replies
10
Views
946
RonPaii
RonPaii
N
Solved In MS Access if the VBA code is taking longer than required how do we force the code to next
Replies
14
Views
803
nector
N
E
Solved Add Record VBA Not working
Replies
18
Views
1,184
E9-Tech
E
D
Solved Code running report despite default set to not
2 3
Replies
43
Views
2,027
RonPaii
RonPaii

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom