Moving from Access BE to SQL - Primary key stopping me from adding new records

[Kev86]

I am in the process of trying to move my Access database BE to an SQL server. This is all very new to me and my knowledge is limited. I have a table called 'Requotes' and when people open the form within MS Access FE they are able to add new records to the Requotes table.

Since I exported my tables to SQL it forced me to add a primary key to the Requotes table and now because their is a primary key, when people open the Requote form it no longer allows them to add a new record. See attached screenshots which show before and after transferring to SQL and the only difference is that the ID field in the Requote tables now has a Primary Key and this is then preventing people adding new lines to the Requote table from a form i have built to enable people to do this.

Any ideas how to get round this issue?

[Bob Fitz]

Perhaps you just need to add the PK to the query and the form.

[Kev86]

Bob, such a simply fix. Thank you very much.

I know it's not ideal as i should probably start a new thread but do you know how I can add a constraint to a date field in SQL to stop dates being entered before 01/01/2020 and no future dates? I have had this as a validation rule built into the BE of an Access database at the table level but i am not too sure how to build this into the table level of the same table in SQL?

[Minty]

You can add a constraint at a table level in SQL server, however if it fails you get an ugly ODBC error which you can't catch in the Access app, which kind of defeats the object.

Better to put the constraint on the data entry/edit form, or if it's used all over the place, build a function that checks it and call it in the various forms before update event.

[Bob Fitz]

Bob, such a simply fix. Thank you very much.

I know it's not ideal as i should probably start a new thread but do you know how I can add a constraint to a date field in SQL to stop dates being entered before 01/01/2020 and no future dates? I have had this as a validation rule built into the BE of an Access database at the table level but i am not too sure how to build this into the table level of the same table in SQL?

I would do the validation in the forms BeforeUpdate event.

EDIT
Oops, I didn't notice Minty's reply.

[Kev86]

Thank you Bob and Minty for your responses. My forms have numerous beforeupdate validation tools however as these are validation rules which are fundamental to the database such as the job number needs to start 85* and be 10 digits long and also all date fields can't be in the future it was just easier to add that at table level then it works throughout. Just trying to avoid having to add that validation into each and every form i have created within the database to be honest. However sounds like it's not that easy to build into SQL at the table level like i did in Access BE.

[Bob Fitz]

Thank you Bob and Minty for your responses. My forms have numerous beforeupdate validation tools however as these are validation rules which are fundamental to the database such as the job number needs to start 85* and be 10 digits long and also all date fields can't be in the future it was just easier to add that at table level then it works throughout. Just trying to avoid having to add that validation into each and every form i have created within the database to be honest. However sounds like it's not that easy to build into SQL at the table level like i did in Access BE.

I think it might be best to create a User Defined Function, as Minty suggested and call that to do the date validation wherever it is required. Post back if you need further assistance.

[Minty]

As I said, you can easily add it at table level, but the error message isn't trappable, as it come back as an ODBC error, not a "Access" error.
I recently updated a large system from Access to SQL Server and had to re-enforce all the table level constraints at a form level to avoid the nasty ODBC message.

If you don't mind your users seeing the ODBC error then add it at the tables level, just be aware it's not very informative, and a bit scary for the uninitiated:

[Kev86]

Thank you both for your responses. I think it looks like although it might be time consuming it might be worth me adding the validation into Form level in the FE rather than in SQL in the BE. If either of you can guide me on what i need to do and what code i will need to create a User Defined Function for forcing all job numbers in the SAP field to start 85* and only be 120 digits long. Also what code will i need to create another function that looks at dates and doesn't allow dates to be entered that is before 01/01/2020 and not in the future.

Really sorry it's been a number of years since i built the original database and i am not a coder so completely out of my depth. I assume once i have the functions built i then need to go to the forms and call upon those functions to address those validation rules?

[Minty]

I would use a boolean functions. They need to be in a general module not a form module.
Something like (this is air code untested)

Public Function fnInvalidDate(dDate As Variant) As Boolean
    ''' Returns True if a date isn't valid
    ''' Usage in a sub - If fnInvalidDate(YourDateToCheck) = True Then ....
       
    If IsNull(dDate) Then           '''' You might want to return False for a null value - your decision
        fnCheckDate = True
        Exit Function
    End If
        
    If Not IsDate(dDate) Then
        fnCheckDate = True
        Exit Function
    End If
    
    If dDate < #1/1/2020# Or dDate > Date Then
        fnCheckDate = True
        Exit Function
    End If
    
    fnCheckDate = False ''' Not really needed as the default value is false but makes things obvious.
    
End Function

A similar type of logic/checking can be applied for your job numbers.

[Kev86]

Hi Minty, thank you for this. I will have a proper look at it next week.

Out of interest how do i then call upon this function once built? Do i call upon this function from the query or from the form? I assume I then need to reference the function against each date field in the Query/Forms with date fields?

Sorry i am not very good at this and it's been a while since i done work on the database i created.

[Minty]

Lets say you have two date controls on a form and want to check them both before saving the data.

In the forms Before Update event you would do do something like

If fnInvalidDate(Me.MyDate1Control) = true or fnInvalidDate(Me.MyDate2Control) = true then 
     msgbox "A date field has an invalid value, please check",vbInformation,"Invalid Date"
     Cancel = True
End If

The Cancel = True will prevent the save from happening, and thus preserve your validation, and prevent duff data entry.
Note: That this is in the forms before update event, not the individual controls.
This is the ultimate and most efficient place to perform validation, as no matter how they try and close the form or save the record it will fire.

You can get cleverer with the messaging (adding up all the validation failures into one message for instance), or perform individual checks rather than pulling them together, but it all depends on how you want to present it to the user.

[Micron]

shouldn't your boolean assignment be passed to fnInvalidDate and not
fnCheckDate ?

[Kev86]

Minty, Micron, thank you both for your responses. I am a lot more confident with just using IF statements built into the BeforeUpdate event rather than having to call upon a function so wouldn't it be easier just to do that instead?

Code:
If Me.EnquiryDate < #1/1/2020# Or Me.EnquiryDate > Now() Then
MsgBox "Enquiry date can't be before 01/01/2020 or a future date"
Cancel = True
End If

I can then also build a bit of code into the SAP field to prevent people using anything over than a job number starting 85 and being 10 digits long...

Code:
If Me.SAP < 8500000000# Or Me.SAP > 8599999999# Then
MsgBox "The SAP number must start 85* and be 10 digits long"
Cancel = True
End If

[Minty]

shouldn't your boolean assignment be passed to fnInvalidDate and not
fnCheckDate ?

Oops yes - that will teach me to change the name in the editor and not check it...
Corrected code below.

Public Function fnInvalidDate(dDate As Variant) As Boolean
    ''' Returns True if a date isn't valid
    ''' Usage in a sub - If fnInvalidDate(YourDateToCheck) = True Then ....
       
    If IsNull(dDate) Then           '''' You might want to return False for a null value - your decision
        fnInvalidDate = True
        Exit Function
    End If
        
    If Not IsDate(dDate) Then
        fnInvalidDate = True
        Exit Function
    End If
    
    If dDate < #1/1/2020# Or dDate > Date Then
        fnInvalidDate = True
        Exit Function
    End If
    
    fnCheckDate = False ''' Not really needed as the default value is false but makes things obvious.
     
End Function