dlookup help

[whojstall11]

Im trying to write a dlookup statement when my Discontinue yes/no check box is checked it will copy one field from one table and put it in another table.
the error i am getting is
Test cant not find the field '|' referred in your expression

Private Sub Discontinue_AfterUpdate()
If Discontinue = -1 Then
tblNotesCurrentMeds.Medications = DLookup("Medications", "Current Meds", "IDCurrentMeds = '" & [IDCurrentMeds] & "'")
tblNotesCurrentMeds.Dosage = DLookup("Dosage", "Current Meds", "IDCurrentMeds = '" & [IDCurrentMeds] & "'")
Else
End If
End Sub

 

[jdraw]

In point form, what exactly are you trying to do?
Are you working with record sets? I ask because the little code you have shown has no recordset info.

What exactly is

tblNotesCurrentMeds.Medications

?
Do you execute an update or append query anywhere in your code?

 

[whojstall11]

Im trying to copy one criteria from one table to another. Basically I have two tables on one form. When i check the yes/no button up my statement should come up. this is my db take a look View attachment test1_be_Backup.zip

tblNotesCurrentMeds.Medications

this was my attempt at telling the db where to store the information.

 

[jdraw]

I have access 2003 so can NOT use or open an accdb formatted database.

You can not simply assign a value to a field in a table as you have shown ( at least as I understand your post). You don't really have tables on forms. You may have a Table as the record source of a form, and you may have controls on the form that are valued with field/values from your table.
I'm not sure if you are creating a new record in the table,or updating a specific record in your table.


You seem to have a "quasi SQL" statement todo the value assignment which is incorrect.

In any event I suggest you create an SQL string in vba representing whatever type of query you need (Append or Update), then

you modify the query based on the

If Discontinue = -1 Then

-- adjust you query as necessary, then
db.execute the appropriate query

See http://www.techonthenet.com/sql/update.php or
http://www.techonthenet.com/sql/insert.php for append query

 

[TPI]

I cant see where you have yes or no
If Discontinue = no
Then -1

 

[John Big Booty]

I cant see where you have yes or no
If Discontinue = no
Then -1

A Check Box can be tested for Checked using; "Yes", -1 or True whilst Uncheck can be tested using; "No", 0 or False.

Note: Yes/No must be enclosed in double quotes whilst True/False doesn't.

 

[John Big Booty]

... however at table level the results are stored as -1 or 0

 

[whojstall11]

So instead of using my tables i create two queries. And I have added an sql to insert the data but it gives me a error "syntax error in the insert statement" here are some screenshots View attachment errormessage .zip

Private Sub Discontinue_Click()
 Dim SQL As String
 If Discontinue = -1 Then
 SQL = "insert into tblNotesCurrentMeds (Medications, Dosage, Frequency, Routine, Date , Notes)" _
 & "select Medications, Dosage, Frequency, Routine, Date, Notes" _
 & "FROM qryCurrentmeds"
 DoCmd.RunSQL SQL

 Else
End If
End Sub