前端编程 - VBA for Access

问：

我在 Visual Basic for Access 中遇到错误。我必须填写一个表格，其中“LocationNote”部分最多可以有 250 个字符，但如果字符数超过 100，我会收到错误。如果它低于 100，我不会收到此错误。

错误：（3421） 应用程序对当前操作使用了错误类型的值。

VBA代码：

Private Sub add_validation_rules_and_text()
    ''Add all validation rules and text

    Me.cmboCollectedByID.ValidationRule = "cmboCollectedByID.Value > 0"
    'If IsNull(cmboCollectedByID) Then
       ' MsgBox "Must provide value for Captured by"
       ' Cancel = True
   ' End If

    If App.getPrimaryRouteValue = QUALITY_CONTROL_VALUE Then
        Me.txtRandomStationName.ValidationRule = "len(txtRandomStationName) = 11"
    Else
        Me.txtRandomStationName.ValidationRule = "len(txtRandomStationName) = 11 and IsAvailableParkwideAssessmentName(txtRandomStationName) = True"
    End If

    Me.txtRandomStationName.ValidationText = "Parkwide Assessment Name must be unique and 11 characters long with the following format: 4 characters for location, 2 characters for the assessment index, an underscore (_), 4 characters for the assessment year (ex: 2020). Completed example: CANA01_2020"

  
    Me.cmboDatumID.ValidationRule = "cmboDatumID > 0"
    Me.cmboDatumID.ValidationText = "Invalid Datum ID"
    Me.txtWaterBodyName.ValidationRule = "len(txtWaterBodyName) <= 50 and len(txtWaterBodyName) > 0"
    Me.txtWaterBodyName.ValidationText = "Water Body Name must be 50 characters or less, and cannot be left empty."
    Me.txtStagingArea.ValidationRule = "len(txtStagingArea) <= 50 or Is Null"
    Me.txtStagingArea.ValidationText = "Staging area must be under 50 characters or left empty."
    Me.txtLocationNote.ValidationRule = "len(txtLocationNote) <= 250 or Is Null"
    Me.txtLocationNote.ValidationText = "Location note must 250 character or less."
    Me.txtEventStartDate.ValidationRule = "IsNull(txtEventStartDate) = false"
    Me.txtEventStartDate.ValidationText = "Please enter an event start date"
    Me.txtEventEndDate.ValidationRule = "IsNull(txtEventEndDate) = false"
    Me.txtEventEndDate.ValidationText = "Please enter an event end date"
    Me.txtWindSpeed.ValidationText = "Please enter a numeric value for wind speed in the range [0.00,60.0] or press escape to enter in a null value"
    Me.txtWindSpeed.Format = "##.0"

    Me.txtAirTemperature.ValidationRule = "is Null OR (txtAirTemperature >= -15.00 AND txtAirTemperature <= 50.00)"
    Me.txtAirTemperature.ValidationText = "Please enter a numeric value for air temperature in the range [-15.00,50.00] or press escape to enter a null value"
    Me.txtAirTemperature.Format = "##.00"

    Me.txtCloudCover.ValidationRule = "is Null or (txtCloudCover >= 0 and txtCloudCover <=100)"
    Me.txtCloudCover.ValidationText = "Please enter a number for cloud cover in the range [0,100] or press escape to enter a null value.:"
    Me.txtCloudCover.Format = "###"
    Me.cmboIsMarineDebris.ValidationText = "The marine debris box should have a value of true or false."
    Me.txtDiscreteSynopticComment.ValidationRule = "Is Null or len(txtDiscreteSynopticComment) <= 250"
    Me.txtDiscreteSynopticComment.ValidationText = "Comment should be under 250 characters long."

    Me.cmboOtherHabitatID.ValidationRule = "Is Null or > 0"
    Me.cmboOtherHabitatID.ValidationText = "Please select a location type or leave this field blank."

End Sub

SQL参数：

ALTER PROCEDURE [dbo].[insertRandomSamplingLocationProc]
    -- Add the parameters for the stored procedure here
    @RandomStationName char(11)
,@CoordinateX varchar(12)
,@CoordinateY varchar(12)
,@UTMZone integer
,@DatumID int
,@WaterBodyName varchar(50)
,@StagingArea varchar(50)
,@IsPlannedLocation bit
,@LocationNote varchar(250)
,@EnteredByID int
,@EnteredDate varchar(100)
,@DataProcessedByID int
,@DataProcessingDate varchar(100)
,@DataProcessingLevelID int
,@RandomStationID int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO [dbo].[RandomSamplingLocation]
           ([RandomStationName]
           ,[CoordinateX]
           ,[CoordinateY]
           ,[UTMZone]
           ,[DatumID]
           ,[WaterBodyName]
           ,[StagingArea]
           ,[IsPlannedLocation]
           ,[LocationNote]
           ,[EnteredByID]
           ,[EnteredDate]
           ,[DataProcessedByID]
           ,[DataProcessingDate]
           ,[DataProcessingLevelID])
     VALUES
           (@RandomStationName
,@CoordinateX
,@CoordinateY
,@UTMZone
,@DatumID
,@WaterBodyName
,@StagingArea
,@IsPlannedLocation
,@LocationNote
,@EnteredByID
,CONVERT(DATE, @EnteredDate)
,@DataProcessedByID
,CONVERT(DATE, @DataProcessingDate)
,@DataProcessingLevelID);

SELECT @RandomStationID = SCOPE_IDENTITY()
RETURN @RandomStationID

END

P.S：谢谢大家;您的提示非常有帮助 - 我能够通过将数据库上的列大小从 100 更改为 250 和所有相关过程来解决它。我只是最大化了表上的列大小，而不是过程。