empty fields

[alkrm]

hi, in my data base there is a form with a command button that run a macro for importing excel file to a table of my data base,
the table is consisting fields that is uploading when running the macro. and there is" date" field which i desire to make the user enter it Manually ,and let it apply to the imported .

at the moment, when importing the date field records are empty.
it is more than 100 fields so it will be stupid to make the user enter it one by one,

is there is a way ,"vb code" that can be input to the command button letting the user write the date? " to a form which will be created for this reason?" and let it apply for the empty records of date field?

this is the command button code

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim xx As String
Dim st As String

'__________Run Import____________
xx= "Import Excel Table"
DoCmd.RunMacro xx
'__________Refresh_______________
DoCmd.Close
st = "FWDTIMSA"
DoCmd.OpenForm st, , , stLinkCriteria



Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

 

[GumbyD]

You can execute an update query in your code after the user has selected a date.



Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim xx As String
Dim st As String
Dim DF as date

'__________Run Import____________
xx= "Import Excel Table"
DoCmd.RunMacro xx
'__________Refresh_______________
DoCmd.Close
st = "FWDTIMSA"
DoCmd.OpenForm st, , , stLinkCriteria
'_________Update the Date_________
DF = Forms![FWDTIMSA]![YourDateFieldName]
CurrentDB.execute "UPDATE YourTableName SET " & _
"YourTableName.YourDateFieldName = #" & DF & "# " & _
"WHERE (((YourTableName.YourDateFieldName) Is Null));"


Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click


You may need to alter it a bit, but I hope you get the idea.

GumbyD

 

[alkrm]

the data are importing , while the date field still empty &
I RECIEVED THIS ERROR MSG:

INVALID USE OF NULL

what i have done is this :
at the form i created a text box ,so the user enter the date
and the code is like this ,


Dim xx As String
Dim st As String
Dim DF as date

'__________Run Import____________
xx= "Import Excel Table"
DoCmd.RunMacro xx
'__________Refresh_______________
DoCmd.Close
st = "FWDTIMSA"
DoCmd.OpenForm st, , , stLinkCriteria

'_________Update the Date_________
DF = Forms![FWDTIMSA]![Text30]
CurrentDb.Execute "UPDATE FWDTIMSA SET " & _
"FWDTIMSA.DATE = #" & DF & "# " & _
"WHERE (((FWDTIMSA.DATE) Is NULL));"


.. where is the mistake , ?

 

[GumbyD]

The only thing I can think of is that it is not seeing your field as a date. Try the following:

Dim xx As String
Dim st As String
Dim DF as date

if isdate(forms![FWDTIMSA]![Text30]) then
DF = cdate(Forms![FWDTIMSA]![Text30])
Else
msgbox "The value you entered is not a date value", vbokonly
Exit sub
End if

'__________Run Import____________
xx= "Import Excel Table"
DoCmd.RunMacro xx
'__________Refresh_______________
DoCmd.Close
st = "FWDTIMSA"
DoCmd.OpenForm st, , , stLinkCriteria

'_________Update the Date_________

CurrentDb.Execute "UPDATE FWDTIMSA SET " & _
"FWDTIMSA.DATE = #" & DF & "# " & _
"WHERE (((FWDTIMSA.DATE) Is NULL));"

Try that and see if it works for you.

GumbyD

 

[alkrm]

same again

well.......
i don't know why but still the same is happening, i still recieve the msg. INVALID USE OF NULL/ records still empty.

this is the DB I created.

if you could help i 'll be thankfull

 

[alkrm]

IT'S WORKING.
THNX ,,,,