Select specific record now selects new record instead

[Perineum]

Ok, I added 2 combo boxes to a form. After the change when I would try to save a record, I would get an error saying "You cannot use a null value for a field that is not a variant data type". I tried to fix it and was unable to. I used a workaround where I added a value to each combobox/table saying "Select..." so the boxes are filled and I can save without the error.

A new problem has come up. When I select a specific record from the main form, it doesn't select the record and instead a new record/form pops up (It should open up the record in the form with the new combo boxes). I ran the debugger and the code is not getting the id from the main form. It used to. WTF? I didn't touch the main form. I can't figure out why it no longer works. Please help. Please.

thanks!

[John_G]

Hi -

Please post the part of the code that is causing problems - without that we can only guess.

How are you selecting a record on the main form, and what happens (code?) when you do? Is there a sub-form involved here?

More info, please!

John

[Perineum]

I was hoping you would just magically know. I'm from Ottawa too. Anyway, here are more details.

On the main form, I select a record by double-clicking on the record or hitting a command button. Here is the code that I didn't touch during my changes:

Private Sub cmdOpen_Click()
On Error GoTo Err_cmdOpen_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm_Incident_Single"

stLinkCriteria = "[INC_id]=" & Me![txtPRnumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpen_Click:
Exit Sub
Err_cmdOpen_Click:
MsgBox Err.Description
Resume Exit_cmdOpen_Click

End Sub

Then the code from the second form with the new comboboxes takes the INC_id and is supposed to open it with that specific record. I ran the debugger and INC_id is null. It used to receive the INC_id. The only part of this procedure that I changed was the V2.2 stuff that I added. Here is the code from the second form:

Private Sub Form_Current()

'Refresh Dropdown list with the corresponding Applications for a specific Group
APP_name_e.RowSource = "SELECT tbl_Application.APP_id, tbl_Application.APP_name_e, tbl_Application.APP_acronym_e FROM tbl_Application WHERE tbl_Application.APP_GRP_id=[GRP_name_e].[Value];"
APP_name_e.Requery

'V2.2 - Refresh Dropdown list with the corresponding Sub-Trends for a specific Trend Category
ST_name_e.RowSource = "SELECT tbl_Sub_Trend.ST_id, tbl_Sub_Trend.ST_name_e, tbl_Sub_Trend.ST_acronym_e FROM tbl_Sub_Trend WHERE tbl_Sub_Trend.ST_TRD_id=[TRD_name_e].[Value];"
ST_name_e.Requery

'Check to make sure we are not at the end of all records and going to a new entry
If IsNull(INC_id) Then bNewIncident = True

'Copy DB data in temporary fields for editing (no changes made directly to database data)
Call GetData

'This is not (or no longer) a new record...
bNewIncident = False

'No change made yet, therefore no need to save
bNeedSave = False

End Sub


Many thanks!

[John_G]

Hi -

First thing I would do is in the main form put msgbox "StlinkCriteria = " & stLinkCriteria right before the DoCmd.openform, to confirm the second form is actually getting a valid value for [INC_Id]. If it isn't then you need to look at why Me![txtPRnumber] does not have a value.

If [INC_ID] is OK before calling the form, then in the On Open event of "frm_Incident_Single", put MsgBox "Filter = " & me.filter, to verify it is getting the filter set properly (no reason why it wouldn't, but you never know...)

Put an Exit Sub right after setting the row sources for the combo boxes.

If the second form is still blank (!), it suggests the underlying table does not have a record that matches the criteria (filter), and you need to check your data.

I'll leave it there for now - let us know how it goes.

John


If the filter is correct, but the form is still

[Perineum]

Thanks for the reply. I added both message boxes and they both popped up with the correct INC_id. I put in the "Exit Sub" but it didn't make any difference. When I open the record the second form is still blank. The two pop-up messages have the correct INC_id. I checked the tables and they have matching values in each table.

One oddity, when I hit the new incident button, the message boxes always pop up with INC_id or filter = 525. Even after I add a new record, when I add another one it doesn't increment and the pop-ups still say 525. The latest INC_id on the table is 556.

You probably saw from the code that the entry for the second combo box is dependant on what value is chosen from the first combobox.

Any idea what is going on?

thanks

[John_G]

I should have thought of this before - what is the setting for the Data Entry property of the Frm_Incident_Single form? It should be No. Is that form going to be used to enter new records, or just to display existing data?

John

[Perineum]

The data entry property of Frm_Incident_Single is No. The form is used for both entering new records and displaying existing data.

[ssanfu]

Are you able or would you be willing to post your project?

[Missinglinq]

Not really sure that it’s the problem, here, but the first thing I see as odd in this is that you have your second bit of code, in the secondary Form, in the Form_Current event, which means that it'll fire every time you move to a New Record. This kind of code is normally run in the Form_Load event, and I'd first try taking out of Form_Current and putting it into Form_Load.
Apart from that, I think the only thing you can really do, here, is to post your app for us to lay our hands on, as Steve suggested.

Linq ;0)>

[Perineum]

I tried moving the code from the Form_Current event to Form_Load. It didn't make a difference.

I'll find out if they will let me post the project. If anyone has more ideas, please let me know.

thanks for all the feedback!

[Perineum]

Unfortunately, I can't post the project.

Any more suggestions would be appreciated.

thanks!

[Perineum]

I found the problem. There were no matching values in the main table. At the suggestion of John_G, I checked that before and I think I must have filled in the last row in the table and then checked the first record from the main form. Unfortunately, the table and form are sorted on different columns so I probably checked the wrong record. Doh!

thanks for the help folks...this one is fixed....not sure how to mark it as solved....

[ssanfu]

Great.

Top of the form "Thread Tools" (second green bar) . Click the dropdown, select "Mark this thread as solved"