possible to enter data into specific row/field box?

[NeedsHelp101]

Hi,

I'm trying to write a code that:
1- Uses DLookup to find a value based on a criteria (which happens to be the primary key)
2- Check if this value is null, and
3- If it is, insert a value into a table from a textbox on a form.

The thing is, the table is already created, so I'm not adding new records to it, I'm just filling in blank boxes.

I can do 1. and 2., but is 3. possible?
Thanks!

 

[Remou]

Yes, it is. You could use a recordset. For example:

'Not real code
Dim rs As DAO.Recordset

Set rs = CurrentDB.OpenRecordset("Select * From tblTable Where Field1 Is Null And ID= " & Me.txtIDNumber)

'Or you could:
'Set rs = CurrentDB.OpenRecordset("Select * From tblTable Where Field1 Is Null")
'but the next bit of code would have to change

If Not rs.EOF Then
   rs.Edit
   rs!Field1=Me.txtField1
   rs.Update
End If

Or you could use SQL:

'Field 1 is numeric
strSQL="Update tblTable Set Field1=" & Me.txtField1 & " Where Field1 Is Null And ID= " & Me.txtIDNumber
'Field 1 is text
strSQL="Update tblTable Set Field1='" & Me.txtField1 & "' Where Field1 Is Null And ID= " & Me.txtIDNumber
'Field 1 is a date
strSQL="Update tblTable Set Field1=#" & Format(Me.txtField1,"yyyy/mm/dd") & "# Where Field1 Is Null And ID= " & Me.txtIDNumber

DoCmd.RunSQL strSQL

 

[NeedsHelp101]

Thanks for the suggestion, Remou.
I'm using

'Field 1 is a date
strSQL="Update tblTable Set Field1=#" & Format(Me.txtField1,"yyyy/mm/dd") & "# Where Field1 Is Null And ID= " & Me.txtIDNumber

DoCmd.RunSQL strSQL

and it keeps breaking at DoCmd, saying that the variable is not defined... I'm not sure what DoCmd is anyway..

 

[rjoubert]

Add .Value to your textbox field references.

Me.txtField1.Value
Me.txtIDNumber.Value

 

[NeedsHelp101]

Ok, so here's my code:

strSQL = "Update tblMasterNames Set FirstUse =#" & Format(Me.boxWeekBegins.Value, "yyyy/mm/dd") & "# Where FirstUse Is Null and MasterName=" & working.Master1.Value
     'strSQL1 =
     DoCmd.RunSQL strSQL

boxWeekBegins is the text box on the form,
MasterName is a field in tblMasterNames
Master1 is a field in the table "working",
FirstUse is the date field that I want to be updated.

When I try to run this code, before updating, the form asks me to enter parameter values, which I shouldn't have to do... what's going on?

 

[Remou]

I do not think that value will help much in this case. You cannot refer to a field in a table as above, that is working.Master1.Value. You need a text box on your form or else you need to Dlookup a table to get Master1.

It is not DoCmd that is the problem, it is the SQL. One good way of testing SQL is to use the immediate window to get a copy of the line (debug.print strSQL) and paste this into the SQL view of the query design screen.

Before Update is not a good evet for this sort of thing.

You will find that DoCmd is very useful indeed, if you look at some of the things you can do with it (F1).