query and check box (yes/no)

[4ukh]

giving me error when compiler run the query i thing the only thing which is messing is the check box yes/no i don't know how to store the check box state into the table field...

please find me the solution...

thank you

Private Sub Chop_it_Click()

   
    Dim sql_val As String
    Dim sql_loop As String
    Dim frm_serv_ip As String
    Dim frm_serv_port As Integer
    Dim frm_CRID As String
    Dim frm_num_chop As Integer
    Dim frm_Uprice As Integer
    Dim frm_payment_rece As Boolean
    Dim frm_start As Date
    Dim frm_exp As Date
    Dim Vtemp As Integer
    Dim x As Integer
    Dim y As Integer
    
    
    
    
    'Here first we check whether or not if any record exists in the table
    If CurrentDb.OpenRecordset("SELECT COUNT(CRID_index.CRID_num) FROM CRID_index")(0) > 0 Then
        Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_index.CRID_num) FROM CRID_index")(0)
    Else
        Vtemp = 0
    End If
    
    'assign chop gear form values to vb variables "frm*"
    frm_serv_ip = cg_serv_ip
    frm_serv_port = cg_serv_port
    frm_CRID = cg_CRID
    frm_num_chop = cg_chp_num
    frm_Uprice = cg_unit_price
    frm_payment_rece = cg_p_chk
    frm_start = cg_start
    frm_exp = cg_exp

    
    x = 0
    y = frm_num_chop
    
    Do While x < y
    x = x + 1
    frm_num_chop = frm_num_chop + 1
    DoCmd.RunSQL "INSERT INTO CRID_index ([CRID], [CRID_num], [server_ip], [server_port], [payment], [validation_period], [start_date], [exp_date])" & _
                "VALUES ('frm_CRID', 'frm_num_chop', 'frm_serv_ip', 'frm_serv_port', 'frm_Uprice', 'frm_validation', 'frm_start', 'frm_exp');"
    Loop
    
    
End Sub

[4ukh]

Ops! sorry guys unfortunately i posted the the incomplete code here is the complete code...

thanks

Private Sub Chop_it_Click()

   
    Dim sql_val As String
    Dim sql_loop As String
    Dim frm_serv_ip As String
    Dim frm_serv_port As Integer
    Dim frm_CRID As String
    Dim frm_num_chop As Integer
    Dim frm_Uprice As Integer
    Dim frm_payment_rece As Boolean
    Dim frm_start As Date
    Dim frm_exp As Date
    Dim Vtemp As Integer
    Dim x As Integer
    Dim y As Integer
    
    
    
    
    'Here first we check whether or not if any record exists in the table
    If CurrentDb.OpenRecordset("SELECT COUNT(CRID_index.CRID_num) FROM CRID_index")(0) > 0 Then
        Vtemp = CurrentDb.OpenRecordset("SELECT MAX(CRID_index.CRID_num) FROM CRID_index")(0)
    Else
        Vtemp = 0
    End If
    
    'assign chop gear form values to vb variables "frm*"
    frm_serv_ip = cg_serv_ip
    frm_serv_port = cg_serv_port
    frm_CRID = cg_CRID
    frm_num_chop = cg_chp_num
    frm_Uprice = cg_unit_price
    frm_payment_rece = cg_p_chk
    frm_start = cg_start
    frm_exp = cg_exp

    
    x = 0
    y = frm_num_chop
    
    Do While x < y
    x = x + 1
    frm_num_chop = frm_num_chop + 1
    DoCmd.RunSQL "INSERT INTO CRID_index ([CRID], [CRID_num], [server_ip], [server_port], [payment], [payment_rece_chk], [validation_period], [start_date], [exp_date])" & _
                "VALUES ('frm_CRID', 'frm_num_chop', 'frm_serv_ip', 'frm_serv_port', 'frm_Uprice', 'frm_payment_rece', 'frm_validation', 'frm_start', 'frm_exp');"
    Loop
    
    
End Sub

[QVeen72]

Hi,

In Access, you need to store
-1 value for Checked,
and 0 for UnChecked

Regards
Veena

[4ukh]

ok but how i don't the procedure please write me some example...

thank you

[QVeen72]

Hi,

Change your code to :

If chkPayRec.Value =vbChecked Then
    frm_payment_rece = -1
Else
    frm_payment_rece =0
End If
 DoCmd.RunSQL "INSERT INTO CRID_index ([CRID], [CRID_num], " _
& " [server_ip], [server_port], [payment], [payment_rece_chk], " _
& " [validation_period], [start_date], [exp_date])"  _
& " VALUES ('" & frm_CRID & "', '" & frm_num_chop & "','" _
&  frm_serv_ip & "', " & frm_serv_port & ", " _
& frm_Uprice & ", " & frm_payment_rece & ", '" _
& frm_validation & "', '" & frm_start & "', #" & frm_exp & "#)"

Change CheckBox, name accordingly..

Regards
Veena

[4ukh]

thanks for the help...

regards

[4ukh]

what data type i must use for the check box in the vb code... for example in form my check box has a name chkbox1 and i want to assign chkbox1's value to the variable called chkvar...

Dim chkvar As Boolean
chkvar = chkbox1

is this OK or not I've to change the data type Boolean to something else...?

and i've one more question in my form i've a combo box which have some values which are given by me like cities names NY, LA and xyz.

city_name which is combo box i want to assign its one value which user select to variable city_temp.

Dim city_temp As Text
city_temp = city_name

is that fine or not...?

thank you
regards

4ukh