Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating empty field in access table with text

Status
Not open for further replies.

jabenj

Technical User
Joined
Dec 18, 2008
Messages
20
Location
US
Hi. I'm a novice trying to update a field (LotNum) in an access table (Product Input) using an update statement. I'm updating through a form that the user fills out under two conditions: 1. the LotNum field is empty, and 2. the appropriate tank is selected. First I was getting a Write conflict error until i entered the null statement. Now i get an "object required" error.



Dim stSql As String
Dim rs As Object
Dim con As Object

Set con = Application.CurrentProject.Connection
txtLotNum.SetFocus
stSql = "Update [Blendsheet Input] "
stSql = stSql & "set [LotNum] = '" & txtLotNum.Text & "'"
stSql = stSql & "where [TankNum] = '" & cboTankNum.Value & "';" & LotNum is Null

Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 3 ' 3 = adModeReadWrite

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunCommand acCmdClose

Set rs = Nothing
Set con = Nothing

Exit_cmdSave_Close_Click:
Exit Sub

Err_cmdSave_Close_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Close_Click
End Sub


Any help would be greatly appreciated
 

Perhaps change this
Code:
stSql = stSql & "where [TankNum] = '" & cboTankNum.Value & "';" & LotNum is Null
to this
Code:
stSql = stSql & "where [TankNum] = " & cboTankNum.Value & "

Can't see any reason to include LotNum Is Null.
The single quotes are delimiters for text fields.
You don't want them if cboTankNum is a number.


Randy
 
Thanks for your help. cboTankNum.Value is a combo box that is selected by the user in the form to update the appropriate table row. I only wanted the row to update if the LotNum field did not have a value. If the LotNum field contains a value, this means it was updated before and shouldn't be updated again. I tried what you suggested but I get a "No value given" message
 
Try this:
Code:
'[green] txtLotNum.SetFocus[/green] [red]why[/red]
'[green] assumes LotNum and TankNum are both text[/green]
 stSql = "Update [Blendsheet Input] "
 stSql = stSql & "set [LotNum] = '" & Me.txtLotNum & "'[highlight] [/highlight]"
 stSql = stSql & "where [TankNum] = '" & cboTankNum.Value & "' AND LotNum is Null"



Duane
Hook'D on Access
MS Access MVP
 
Thanks all for help. I got it to work. The problem was that "txtLotNum" was a bounded field. I changed it to an unbounded text and it works now. Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top