SQL Backend, New Record Doesn't Create New Entry

[jcruzAME]

Let me explain. So the old version of the program we're working with, one form is pulling from a table. If you hit new record, it assigns it a new ID in that table (even though it says the ID is null when it loads, I have a message box pop up on current and it always says Null).

Anyway, in the new one with an SQL backend, on the same form, if I click on new record, the ID (the column is called AssetLiabilityID) stays Null no matter what I do. Am I missing something?

Also, I might try to filter the recordset based on a Client ID, but the same thing happens when I try to create a new record, no ID.

If this would be answered better in another section of these forums, please let me know.

 

[pbaldy]

If I'm understanding correctly, you may be running into a difference between an Access Autonumber field and a SQL Server Identity field (its autonumber equivalent). Basically, the Access autonumber is generated as soon as you start creating a record. The SQL Server identity value is not generated until the record is saved.

Posting your method might yield a workaround.

 

[jcruzAME]

Well, one of the combo boxes requires a pull on the AssetLiabilityID (the primary key that should be autonumbered), and while trying to create a new record it is always null in the new program.

I tried saving the record before doing that part of the code and it seemed to work but whenever I get done filling out the form and close it out to save it, it doesn't reappear when I pull that Client up again (it pulls the assets for a specified client).

 

[pbaldy]

Hard to say without seeing it. Presuming the control is bound to the the field in the table, it should certainly be there when you pull the record back up. This isn't a manual calculation of the number, is it? What's the control source of that text box?

 

[jcruzAME]

Well, I just checked the table. It looks like they're in there but it's not saving the Client ID (one of the fields in the AssetLiability table that we filter the records on).

If I'm pulling info from a table (Select * from tblAssetLiability where Client ID = #), which pulls all the assets for a client, if I select new record, shouldn't it be creating a new Asset Record for them?

This is what the old program appears to be doing without being told to anywhere that I can see.

 

[pbaldy]

Can you post the db, or at least a picture of the form in design view?