Insert into the second column not working

[sonikudi]

Hello again,


I have the following code...when i tried inserting to the first column it works splendidly..but when i try inserting to the second column (MfgPcb) in the same table ..it doesn't give me an error but when i check my table it doesn't insert into the column either..

Private Sub AddPCB_Click()
Dim Response
 
 If Me.txtVendor <> "" Then
 Response = MsgBox("Are you sure you want to add this Vendor?", vbYesNo, "Vendor")
    If Response = vbYes Then
    Dim StrSQL As String
 
     StrSQL = "INSERT INTO Manufacturer (MfgPcb) VALUES (""" & Me.txtVendor & """);"
    DoCmd.RunSQL StrSQL
    'DoCmd.SetWarnings False
 
    MsgBox "Vendor Added to DataBase."
    ElseIf Response = vbNo Then
 
    MsgBox "Vendor Not added to Database."
    End If
Else
 MsgBox "Please input Vendor Name in the box.", , "Vendor"
End If

End Sub

Any help would be much appreciated.

thank you!..

 

[cmmrfrds]

After you build the string StrSQL then put in a debug statement to look at it's content.

Debug.Print StrSQL

Then do a Control G and look in the immediate window and see if the string is built correctly. If you need further assistance then paste in the string here.

 

[sonikudi]

Hi cmmrfrds,

Thanks for suggesting a response. The string indeed is built correctly...this is what i got from the immidiate window

INSERT INTO Manufacturer (MfgPcb) VALUES ("Shlagha");

 

[randy700]

Is there a required field that you are not populating?

Randy

 

[sonikudi]

I don't think so. There are only two columns in this table. Mfg and MfgPCB. Mfg is the primary key in this table.. has about 7 rows with data in it ..the MfgPCB field has only 2 rows with data in it...

 

[sonikudi]

just for clarification..the remaining 5 rown in MfgPCB are empty. Is Mfg being the primary key a problem?

 

[HarleyQuinn]

So, you're trying to insert a row, but not specifying the required Primary key field?

Yes, this will be a problem. If you know the value of Mfg that you want the MfgPCB associated with just do an update query specifying that Mfg in the WHERE statement.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[randy700]

Are you UPDATING an existing row or trying to ADD a new row?


Randy

 

[sonikudi]

Thats a good point...well see the Mfg and MfgPCB are not really related to each other..they both just have vendor names in them pertaining to different categories. So maybe i should not have a primary key in this table..


Because MfgPCB still has 5 rows empty..i guess i should be updating it and not adding a row..

thanks for the help.

 

[sonikudi]

I just realized that UPDATING wouldn't work especially if i don't hav a where clause that would hod true all the time...so i removed the primary key and now although i can add a vendor to 'MfgPcb' column, instead of adding the vendor in one of the empty rows it just starts a new row..where there is a value in MfgPcb but no value for that row in column Mfg...Are there any serious drawbacks to this?

 

[randy700]

the Mfg and MfgPCB are not really related to each other

Then why would they be in the same record? You need to normalize your database.


Randy

 

[HarleyQuinn]

Generally yes, rows are data generally relating to each other. If you have to query things as they are it'll be interesting.

Easiest thing to do? Store the data in two tables (preferrably with an ID of some kind). You can always do an insert then and unrelated data won't be held together.

I'm not going to go into DB design (it's been too long since I've had to do it ) but have a read up on Normalisation.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Great minds eh Randy?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[sonikudi]

Yea i know about normalization, took a VB/SQL course a while back, but for some reason didn't think about it...because these columns in this table are control source to many other forms..changing them will be A BIG PIA...

But thanks for the suggestions, For next time its a great lesson..!