Locking List Box Value after adding first record

[ppatel2]

So I have this listbox that I set to a country. I also have two text boxes where I enter in information. Currently the List box reset after each submit command. I was wondering if I could lock the value in the list box until I clear all table contents (already have a command button for this)?

 

[kjv1611]

It sounds to me like your "submit" command is what moves on to the next record, and therefore the listbox automatically requeries for a new record. Is this not the case?

 

[ppatel2]

kjv1611,

You are correct. I tried placing an if statement in the onclick event of the command button. It looked like the following:


if (conversion_1.country is null) then (DoCmd.GoToRecord , , acNewRec) (else [list13=conversion_1.country] and [DoCmd.GoToRecord , , acNewRec])

It is erroring out in the else statement. Do you have a suggestion?

 

[ppatel2]

kjv1611,

My ultimate goal is to not have to keep selecting a listbox value when I know all corresponding list box values will equal the initial list box value.

 

[kjv1611]

To possibly fix whatever error you are getting, do you have all of that in your properties for the control, or in a module? What you should do is have the code in a module, and have "[Event Procedure]" in the properties location - in the locatino for the event you are using.

Then your code would look like:

If IsNull(conversion_1.country) Then
  DoCmd.GoToRecord , , acNewRec
Else
  list13=conversion_1.country
  DoCmd.GoToRecord , , acNewRec]
End If

 

[ppatel2]

kjv611,

I pasted the code you provided and the error message I'm getting is "Object Required". I have setup the code in the module corresponding to the command button and event (on-click).

 

[kjv1611]

So, your code is now:

Private Sub CommandButton_Click()
  If IsNull(conversion_1.country) Then
    DoCmd.GoToRecord , , acNewRec
  Else
    list13=conversion_1.country
    DoCmd.GoToRecord , , acNewRec]
  End If
End Sub

Do you have the form's recordset set to a particular table?

 

[ppatel2]

I do have the forms records set to a particular table (input). The table and fields used in the if statement are from a query that updates everytime a row is submitted.

 

[kjv1611]

Is conversion_1.country one whole name for a field or for a control, or is it like this:

conversion_1 = table
country = field

In that case, are you looking for any records that have a null value in the "country" field?

If so, you could use a SQL statement and or possibly a DLOOKUP function.

If this is the case, try looking up DLOOKUP in the VBA helpfile, and it will give you the structure, info, and a couple examples.