Can any one please help me?? Using VB 6.0 and Access DB.

ssql2 = "INSERT INTO JanuaryDay (JanRm_id) SELECT rmd_id FROM RmIdIndex WHERE rm_id= " & CLng(txtrostermid.Text)

    rsName.Open ssql2, conName, adOpenDynamic, adLockOptimistic
    
    rsmnth.Open "select * from JanuaryDay", conName, adOpenDynamic, adLockOptimistic

rsmnth.MoveFirst
Do While Not rsmnth.EOF
	‘here is the problem.                                                              
If rsmnth("JanRm_id") = txtrmdid.Text And rsmnth("JanDate") = Null Then
'in DB JanRm_id(column) of JanuaryDay(table) will contain duplicate 'value. ‘That’s what after inserting data in JanRm_id I want to pick 'the row which ‘having data in JanRm_id and no data in JanDate, so 'that I can input data in ‘that specific row. Cannot use UPDATE 'command coz JanRm_id have duplicate ‘value.
'Same thing I did with number data type. Like:

'ssql2 = "INSERT INTO UserOfTheMonth (emp_id) SELECT Emp_id 'FROM ‘users where Name='" & CboName.Text & "'"

'    rsName.Open ssql2, conName, adOpenDynamic, adLockOptimistic

'rsmnth.Open "select * from UserOfTheMonth", conName, 'adOpenDynamic, ‘adLockOptimistic

'rsmnth.MoveFirst
'Do While Not rsmnth.EOF
'If rsmnth("year") = 0 And rsmnth("emp_id") = TxtEmpId.Text Then
'txtrostermid.Text = rsmnth("rosterm_id")
'rsmnth.Fields("month").Value = Trim(Cbomnth.Text)
'rsmnth.Fields("year").Value = Val(Cboyear.Text)
'rsmnth.Update
'End If
'  rsmnth.MoveNext
'   Loop

'Here year(column) of UserOfTheMonth having number data type. 'But above one (rsmnth("JanDate") = Null) is having Text data type.
'Vary new with VB6.0. Hitting my head to wall for the last 2 days.
'Can any one help me please

rsmnth.Fields("JanDate").Value = Trim(Label1.Caption)
rsmnth.Fields("JanShift").Value = Trim(Combo1.Text)
rsmnth.Update
End If
  rsmnth.MoveNext
   Loop
    End If

regards

Hi

Use

Dim sJanDate as String

'Retrieve empty string if the record is null
sJanDate = rsmnth("JanDate") & ""

'Then check it
If sJanDate = "" .... Then
....
End if
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.