Edit a table that a form is not connected to

Topic

New Reply

Hi,

I’m got a form that has cmdClosePO on it. The form is bound to table shipping/invoice. When the user clicks the button they want [closed] which has yes/no format, in purchase maked as true. The way to identify that the correct record is being marked is the [po order] in purchases =txtpo on the receiving form. I’m not sure how to code this.

Thanks!
Leesha

Reply | Quote

Replies

You can run an SQL statement to update the purchase table:

Dim strSQL As String
strSQL = “UPDATE Purchase SET Closed = True WHERE [po order] = ” & Me.txtPO
CurrentDb.Execute strSQL, dbFailOnError

This assumes that po order is a number field. If it is a text field, change the line that sets strSQL as follows:

strSQL = “UPDATE Purchase SET Closed = True WHERE [po order] = ” & Chr(34) & Me.txtPO & Chr(34)

Reply | Quote

You can run an SQL statement to update the purchase table:

Dim strSQL As String
strSQL = “UPDATE Purchase SET Closed = True WHERE [po order] = ” & Me.txtPO
CurrentDb.Execute strSQL, dbFailOnError

This assumes that po order is a number field. If it is a text field, change the line that sets strSQL as follows:

strSQL = “UPDATE Purchase SET Closed = True WHERE [po order] = ” & Chr(34) & Me.txtPO & Chr(34)

Hi Hans,

PO Order is an autonumber. I’m not sure is the makes a difference or not. I used the first part of the code that you gave me, making one change. The table is called “Purchases”. I had left out an “s”. Anyway, I get an error that says “Too few parameters. Expected 1”. I’m not sure what its looking for or if the autofield makes a difference.

Thanks,
Leesha

Reply | Quote

Also make sure that the names closed and po order are spelled EXACTLY as in the table design.

Reply | Quote

Also make sure that the names closed and po order are spelled EXACTLY as in the table design.

I think you need

strSQL = “UPDATE Purchases SET Closed = True WHERE [po order] = ” & Me.txtPO

Since PO is a number field it does not need the ” chr(34)

Reply | Quote

Hi,

I’m got a form that has cmdClosePO on it. The form is bound to table shipping/invoice. When the user clicks the button they want [closed] which has yes/no format, in purchase maked as true. The way to identify that the correct record is being marked is the [po order] in purchases =txtpo on the receiving form. I’m not sure how to code this.

Thanks!
Leesha

There’s going to be a variety of possible answers to this so I am only going to give one possible option.
You may have to change field names to suite your application.

But assuming Purchase Order Table is called Purchases and [closed] is the yes no field, then something like this in the
Buttons on Click Event

Note I have not added any error trapping to this which really you ought to do.

Dim dbs as Dao.Database, rst As Dao.Recordset

‘Check they meant it
If msgbox(“Do you really want to close this Order”,vbYesNo+vbQuestion+vbDefaultButton1,”Close PO”)=vbNo then Exit Sub

‘Check OK with valid PO Number
If txtPO & “” “” Then
set dbs=CurrentDB
set rst=dbs.OpenRecordset(“Select * From PURCHASE Where [po order]=” & txtPO)
If not rst.eof then
rst.edit
rst![closed]=True
rst.Update
Endif
rst.close
dbs.close
set rst=Nothing
set dbs=Nothing
msgbox “Purchase Order Closed”,vbExclamation
Endif

Please note that in the above if the Purchase Order No is Not Numeric but alpha numeric then the Open Recordset line should read

set rst=dbs.OpenRecordset(“Select * From PURCHASE Where [po order]='” & txtPO & “‘”)

As I say one method of many

Reply | Quote