Using DLOOKUP in a textbox (1 Viewer)

[DavidAtWork]

I haven't seen your database and I'm not entirely clear on what you're trying to do, are you indending to create new records in a table from what is currently showing on this form as it sounds like the form is running off a recordset
David

 

[pwalter83]

1. You should not be saving calculated values. That's why they are calculated so you can do it on-the-fly.

2. It will only change when you type something new into Text41 AND move to another control.

Thanks,

1. well actually I am trying to save data from the ms access form to a table. What I noticed that when I press Save button the form for the first time it gets saved as a row in the table. But when I try to add another row through the form, the first row that was created gets overwritten.

2. The data saved from the 2 auto filled textboxes on the form does not get updated on the table. The values in the table do not get updated from the 2 auto filled textboxes and contain the same values from previous entry.

I hope I am able to make it more clear.

 

[vbaInet]

1. How are you adding a new record?

2. This goes back to my point, i.e. point 1, in my last post regarding not saving calculated values.

 

[pwalter83]

1. How are you adding a new record?

2. This goes back to my point, i.e. point 1, in my last post regarding not saving calculated values.

A record is created by filling in all the textboxes on the form and then saving by clicking on the Save button. This should create a new row of data in the table located in SQL.

 

[pwalter83]

I haven't seen your database and I'm not entirely clear on what you're trying to do, are you indending to create new records in a table from what is currently showing on this form as it sounds like the form is running off a recordset
David

Thanks for the reply. The form is based on and has been created using a table - VESSEL and is located in SQL Server. The form has exactly the same no of data entry fields as the table including 2 auto filled textboxes which are auto filled using a combo box.

Please find the form attached which would make it more clear.

 

[DavidAtWork]

It sounds to me like your form is bound to a table and when you think you are adding another record, the record id hasn't changed and all you're doing is editing the same record. You need to navigate to a new record you want to edit, do your lookups and save the new data. Saving should be done by using an update query or sql using the fields on your form as a base for the updated values WHERE the record id in the table = the record id on the form
David

 

[pwalter83]

It sounds to me like your form is bound to a table and when you think you are adding another record, the record id hasn't changed and all you're doing is editing the same record. You need to navigate to a new record you want to edit, do your lookups and save the new data. Saving should be done by using an update query or sql using the fields on your form as a base for the updated values WHERE the record id in the table = the record id on the form
David

basically the requirement is to enter new records into the table. however, if the values for the combination of three primary fields in the form already exist in the table, then the record should be updated.

There is already a trigger in place on the table which updates records in the table.

 

[pwalter83]

It sounds to me like your form is bound to a table and when you think you are adding another record, the record id hasn't changed and all you're doing is editing the same record. You need to navigate to a new record you want to edit, do your lookups and save the new data. Saving should be done by using an update query or sql using the fields on your form as a base for the updated values WHERE the record id in the table = the record id on the form
David

I am using this VB code to save data:
-------------------------------------------
Private Sub Save_Click()
Dim strMsg As String, strTitle As String
strMsg = "Do You Want To Save This Record?"
strTitle = " Save Record ?"
If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
Me.Undo
End If
DoCmd.GoToRecord record:=acNewRec
End Sub
--------------------------------------------
is this correct?

previously I was using this code but it did not work as desired:
-------------------------------------------
Private Sub Save_Click()
If Me.Dirty Then Me.Dirty = False
End Sub
-------------------------------------------

what I also noticed that I get this error Run-time error '2105':

You can't go to the specified record.

 

[vbaInet]

DavidAtWork has told you the sequence of events. It seems you don't understand how data is being saved.

It's a simple concept. If you see data and write over it and then press the save button, what do you expect will happen?

 

[pwalter83]

DavidAtWork has told you the sequence of events. It seems you don't understand how data is being saved.

It's a simple concept. If you see data and write over it and then press the save button, what do you expect will happen?

I understand what you are saying but I need to work around this so that the data is not overwritten but added as a new row in the table.

This is the reason I am using Add Record now instead of Save but I dont know what VB code would need to be input to get the Add Record button working.

Do you have any idea on this one ?

 

[DavidAtWork]

if all the values you want to save are coming from your form, then you can run some vba sql to add the record, something like:
Docmd.runsql("INSERT INTO tblVESSEL(field1, field2, field3) " _
& "VALUES(""" & Me.Text1 & """, """ & Me.text2 & """,""" & Me.Text3 & """)")
This is only sample code to give you an idea and the syntax around the objects in the VALUES section are a bit tricky to get right depending on the data types, the ones above are for string types.
Your code above just seems to edit an existing record and if user says NO to saving, it tries to undo but you can't use Me.Undo, your code then goes on to insert a new blank record, it doesn't insert what you think you've just created from some lookups
David

 

[vbaInet]

This is the reason I am using Add Record now instead of Save but I dont know what VB code would need to be input to get the Add Record button working.

Drop a Button on your form and follow the Button Wizard. Look through the options available under Record Operations in the wizard.

You first CREATE (or move to) a new record, enter the details for that record, then SAVE. That's the sequence.

 

[pwalter83]

Drop a Button on your form and follow the Button Wizard. Look through the options available under Record Operations in the wizard.

You first CREATE (or move to) a new record, enter the details for that record, then SAVE. That's the sequence.

I have created a form which adds row of data (record) to a table. I am able to test the form but the problem now is that when I try to go to Design View, it gives an error:

------------------------------------------
ODBC --call failed

Cannot Insert the value NULL into the column. Column does not allow nulls. INSERT fails. The statement has been terminated.

------------------------------------------

When this error starts to appear, I am unable to view the form in design view or save it. But I am able to make entries to the table.

I am using this VBA code for the 'Add Record' button:
----------------------------------------------------------
Private Sub Add_Record_Click()
Dim strSQL As String

strSQL = "INSERT INTO dbo_VESSEL (VESSEL_NAME, VESSEL_CD,VOYAGE_NUM, LINE_CD, PORT_CD,DEPART_ACTUAL_DT,DIVISION_CD ) VALUES ('" & Me!VESSEL_NAME & "', '" & Me!VESSEL_CD & "','" & Me!VOYAGE_NUM & "', '" & Me!Text41 & "', '" & Me!PORT_CD & "', '" & Me!DEPART_ACTUAL_DT & "', '" & Me!Text45 & "');"
DoCmd.RunSQL strSQL
End Sub
------------------------------------------------------------

I have attached the screenshot of the error.

Many thanks for your help !

 

[vbaInet]

You need to test each field or control to ensure that they have a value before running the INSERT statement.

Plus are you sure that all your fields are TEXT fields? I ask this because you have surrounded all the VALUES in single quotes indicating that they are text. Is Voyage_Num text?