C# VBA 積算・原価管理

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace 積算_原価管理
{
    public partial class Form1 : Form
    {
        Boolean isLoad = false;

        private List GetControls(string pattern)
        {
            List l = new List();
            foreach (Control c in this.Controls)
            {
                if (pattern == "DecimalText")
                {
                    switch (c.Name)
                    {
                        case "textBox14":
                        case "textBox15":
                        case "textBox6":
                        case "textBox8":
                        case "textBox9":
                        case "textBox10":
                        case "textBox11":
                        case "textBox12":
                        case "textBox13":
                            l.Add(c);
                            break;
                    }
                }
                else if(pattern == "ProductInputs")
                {
                    switch (c.Name)
                    {
                        case "textBox1":
                        case "comboBox1":
                        case "comboBox2":
                        case "comboBox3":
                        case "textBox14":
                        case "textBox15":
                        case "textBox4":
                        case "textBox5":
                        case "textBox6":
                        case "textBox7":
                        case "textBox8":
                        case "textBox9":
                        case "textBox10":
                        case "textBox11":
                        case "textBox12":
                        case "textBox13":
                        case "textBox16":
                            l.Add(c);
                            break;
                    }
                }
                else if (pattern == "PartsInput")
                {
                    switch (c.Name)
                    {
                        case "textBox2":
                        case "textBox18":
                        case "textBox19":
                        case "textBox20":
                        case "textBox21":
                        case "textBox3":
                        case "textBox17":
                            l.Add(c);
                            break;
                    }
                }
            }
            return l;
        }

        private string BuildQuery(string pattern)
        {
            if (pattern == "新規")
            {
                return "INSERT INTO 製品 (向先,型式,製作先,販売価格,営業手配分,外注費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考) VALUES (" +
                        "'" + comboBox1.Text + "'," +
                        "'" + comboBox2.Text + "'," +
                        "'" + comboBox3.Text + "'," +
                        "'" + textBox14.Text + "'," +
                        "'" + textBox15.Text + "'," +
                        "'" + textBox6.Text + "'," +
                        "'" + textBox8.Text + "'," +
                        "'" + textBox9.Text + "'," +
                        "'" + textBox10.Text + "'," +
                        "'" + textBox11.Text + "'," +
                        "'" + textBox12.Text + "'," +
                        "'" + textBox13.Text + "'," +
                        "'" + textBox16.Text + "')";
            }
            else if (pattern == "更新")
            {
                return "UPDATE 製品 SET " +
                        "向先 = '" + comboBox1.Text + "'," +
                        "型式 = '" + comboBox2.Text + "'," +
                        "製作先 = '" + comboBox3.Text + "'," +
                        "販売価格 = '" + textBox14.Text + "'," +
                        "営業手配分 = '" + textBox15.Text + "'," +
                        "外注費 = '" + textBox6.Text + "'," +
                        "輸送費荷受 = '" + textBox8.Text + "'," +
                        "輸送費納品 = '" + textBox9.Text + "'," +
                        "試験費 = '" + textBox10.Text + "'," +
                        "品証費用 = '" + textBox11.Text + "'," +
                        "品証経費 = '" + textBox12.Text + "'," +
                        "社内製作分 = '" + textBox13.Text + "'," +
                        "備考 = '" + textBox16.Text + "' " +
                        "WHERE ID = " + textBox1.Text + "";
            }
            else if (pattern =="削除")
            {
                return "DELETE FROM 製品 WHERE ID = " + textBox1.Text + "";
            }
            return "";
        }

        private void InitGridView()
        {
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            dataGridView1.AllowUserToAddRows = false;
            //dataGridView1.ReadOnly = true;

            dataGridView1.Font = new Font("MS UI Gothic", 12);

            dataGridView1.Columns.Add("部品表ID", "部品表ID");
            dataGridView1.Columns.Add("集計ID", "集計ID");
            dataGridView1.Columns.Add("部品ID", "部品ID");
            dataGridView1.Columns.Add("名称", "名称");
            dataGridView1.Columns.Add("材質", "材質");
            dataGridView1.Columns.Add("寸法", "寸法");
            dataGridView1.Columns.Add("単価", "単価");
            dataGridView1.Columns.Add("数量", "数量");

            DataGridViewButtonColumn col = new DataGridViewButtonColumn();
            col.Name = "更新";
            col.UseColumnTextForButtonValue = true;
            col.Text = "更新";
            dataGridView1.Columns.Add(col);

            col = new DataGridViewButtonColumn();
            col.Name = "削除";
            col.UseColumnTextForButtonValue = true;
            col.Text = "削除";
            dataGridView1.Columns.Add(col);

            dataGridView1.Columns["部品表ID"].Visible = false;
        }

        public Form1()
        {
            InitializeComponent();

            LoadMaster();
            InitGridView();

            //読込
            button1.Click += new EventHandler((object sender ,EventArgs e) => { LoadTable("読込"); });
            //検索
            button2.Click += new EventHandler((object sender, EventArgs e) => { LoadTable("検索"); });
            //一覧
            button8.Click += new EventHandler((object sender, EventArgs e) => {
                Form f = new Form3(this);
                f.StartPosition = FormStartPosition.Manual;
                f.Location = new Point(Cursor.Position.X + 50, Cursor.Position.Y);
                f.ShowDialog();
            });

            //新規
            button7.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("新規"); });
            //更新
            button4.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("更新"); });
            //削除
            button6.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("削除"); });

            //部品ID検索
            button5.Click += new EventHandler((object sender, EventArgs e) => {
                Form f = new Form2(this);
                f.StartPosition = FormStartPosition.Manual;
                f.Location = new Point(Cursor.Position.X + 50,Cursor.Position.Y - 550);
                f.ShowDialog();
            });

            //部品追加
            button3.Click += new EventHandler(AddRow);

            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

            textBox1.ForeColor = Color.Red;

            textBox1.TextChanged += new EventHandler((object sender, EventArgs e) => {
                isLoad = false;
                textBox1.ForeColor = Color.Red;
                
            });
        }

        private Boolean TryDecimal()
        {
            try
            {
                foreach (Control c in GetControls("DecimalText"))
                {
                    if (c.Text == "") c.Text = "0";
                    Decimal.Parse(c.Text);
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        
        private Boolean ProductsInputCheck(string sender)
        {
            if (sender == "新規")
            {
                if (textBox1.Text != "")
                {
                    MessageBox.Show("製品IDが入力されています。");
                    return false;
                }
                if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")
                {
                    MessageBox.Show("向先・型式・製作先が空白です");
                    return false;
                }
                if (!TryDecimal())
                {
                    MessageBox.Show("金額が正しくありません。");
                    return false;
                }
            }
            else if (sender == "更新")
            {
                if (textBox1.Text == "")
                {
                    MessageBox.Show("製品IDが入力されていません。");
                    return false;
                }
                if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")
                {
                    MessageBox.Show("向先・型式・製作先が空白です");
                    return false;
                }
                if (!isLoad)
                {
                    MessageBox.Show("製品IDが正しくない可能性があります。");
                    return false;
                }
                if (!TryDecimal())
                {
                    MessageBox.Show("金額が正しくありません。");
                    return false;
                }
            }
            else if (sender == "削除")
            {
                if (textBox1.Text == "")
                {
                    MessageBox.Show("製品IDが入力されていません。");
                    return false;
                }
                if (!isLoad)
                {
                    MessageBox.Show("製品IDが正しくない可能性があります。");
                    return false;
                }
            }
            return true;
        }

        private void DataChanges(string sender)
        {
            if (!ProductsInputCheck(sender)) return;

            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
            {
                try
                {
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand(BuildQuery(sender), con);
                    cmd.ExecuteNonQuery();

                    if (sender == "新規")
                    {
                        cmd = new OleDbCommand("SELECT @@IDENTITY", con);
                        using (OleDbDataReader dr = cmd.ExecuteReader())
                        {
                            if (dr.Read()) textBox1.Text = dr[0].ToString();
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    return;
                }
                finally
                {
                    con.Close();
                }
            }

            LoadMaster();

            if (sender == "削除")
            {
                MessageBox.Show("削除しました。");
                InputClear();
            }
            else
            {
                LoadTable(sender);
            }
        }

        private void dgvClick(object sender, DataGridViewCellEventArgs e)
        {
            int col = e.ColumnIndex;
            int row = e.RowIndex;

            //削除
            if (col == 9 && row >= 0)
            {
                if (!isLoad)
                {
                    MessageBox.Show("製品IDが正しくない可能性があります。");
                    return;
                }

                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
                {
                    try
                    {
                        con.Open();
                        OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品表 WHERE 部品表ID = " + id + "", con);
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        MessageBox.Show("データベースに接続できませんでした。");
                        return;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                LoadTable("削除");
            }

            //更新
            if (col == 8 && row >= 0)
            {
                if (!isLoad)
                {
                    MessageBox.Show("製品IDが正しくない可能性があります。");
                    return;
                }

                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();
                string quantity = dataGridView1.Rows[row].Cells[7].Value.ToString();
                string calculateId = dataGridView1.Rows[row].Cells[1].Value.ToString();

                try
                {
                    decimal.Parse(quantity);
                    int i = int.Parse(calculateId);
                    if (i > 5 || i < 1) throw new Exception(); } catch { MessageBox.Show("集計ID・数量が正しくありません。"); return; } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand( "UPDATE 部品表 SET 集計ID = " + calculateId + ", 数量 = " + quantity + " WHERE 部品表ID = " + id + "", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); InputClear(); return; } finally { con.Close(); } } LoadTable("更新"); } } private void InputClear() { dataGridView1.Rows.Clear(); foreach (Control c in GetControls("ProductInputs")) { c.Text = ""; } } private void LoadMaster() { comboBox1.Items.Clear(); comboBox2.Items.Clear(); comboBox3.Items.Clear(); using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd; OleDbDataReader dr; foreach (string str in new string[] {"向先","型式","製作先" }) { cmd = new OleDbCommand("SELECT DISTINCT " + str +" FROM 製品", con); dr = cmd.ExecuteReader(); while(dr.Read()) { if(str == "向先") this.comboBox1.Items.Add(dr["向先"].ToString()); if(str == "型式") this.comboBox2.Items.Add(dr["型式"].ToString()); if(str == "製作先") this.comboBox3.Items.Add(dr["製作先"].ToString()); } } } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } } private Boolean TryInt() { try { decimal.Parse(textBox17.Text); int i = int.Parse(textBox3.Text); if (i > 5 || i < 1) return false; } catch { return false; } return true; } private Boolean PartsInputCheck() { if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox17.Text == "") { MessageBox.Show("製品ID・部品ID・集計ID・数量を入力してください。"); return false; } if (!isLoad) { MessageBox.Show("製品IDが正しくない可能性があります。"); return false; } if (!TryInt()) { MessageBox.Show("集計ID・数量が正しくありません。"); return false; } return true; } private void AddRow(object sender, EventArgs e) { if (!PartsInputCheck()) return; using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品表 (製品ID,集計ID,部品ID_,数量) VALUES (" + textBox1.Text + "," + textBox3.Text + "," + textBox2.Text + "," + textBox17.Text + ")", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } foreach (Control c in GetControls("PartsInput")) { c.Text = ""; } LoadTable("追加"); } private void LoadTable(string sender) { dataGridView1.Rows.Clear(); if(sender == "読込") { if (this.textBox1.Text == "") { MessageBox.Show("IDを入力してください。"); InputClear(); return; } } else if (sender == "検索") { if (this.comboBox1.Text == "" || this.comboBox2.Text == "" || this.comboBox3.Text == "") { MessageBox.Show("向先・型式・製作先を入力してください。"); InputClear(); return; } } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); string q = ""; switch (sender) { case "検索": case "新規": q = "SELECT * FROM 製品 WHERE 向先 = '" + comboBox1.Text + "' AND 型式 = '" + comboBox2.Text + "' AND 製作先 = '" + comboBox3.Text + "'"; break; case "読込": case "削除": case "追加": case "更新": q = "SELECT * FROM 製品 WHERE ID = " + this.textBox1.Text + ""; break; } OleDbCommand cmd = new OleDbCommand(q, con); OleDbDataReader dr = cmd.ExecuteReader(); if (!dr.Read()) { MessageBox.Show("データがありませんでした。"); InputClear(); return; } else { int i = 0; do { this.textBox1.Text = dr["ID"].ToString(); this.comboBox1.Text = dr["向先"].ToString(); this.comboBox2.Text = dr["型式"].ToString(); this.comboBox3.Text = dr["製作先"].ToString(); this.textBox14.Text = string.Format("{0:f0}", dr["販売価格"]); this.textBox15.Text = string.Format("{0:f0}", dr["営業手配分"]); this.textBox6.Text = string.Format("{0:f0}", dr["外注費"]); this.textBox8.Text = string.Format("{0:f0}", dr["輸送費荷受"]); this.textBox9.Text = string.Format("{0:f0}", dr["輸送費納品"]); this.textBox10.Text = string.Format("{0:f0}", dr["試験費"]); this.textBox11.Text = string.Format("{0:f0}", dr["品証費用"]); this.textBox12.Text = string.Format("{0:f0}", dr["品証経費"]); this.textBox13.Text = string.Format("{0:f0}", dr["社内製作分"]); this.textBox16.Text = dr["備考"].ToString(); i++; } while (dr.Read()); if (i > 1) MessageBox.Show("向先・型式・製作先の組合わせが重複しているので注意してください。");
                    }

                    cmd = new OleDbCommand("SELECT * FROM 部品表 LEFT OUTER JOIN 部品 ON 部品表.部品ID_ = 部品.部品ID WHERE 製品ID = " + this.textBox1.Text + "" , con);
                    dr = cmd.ExecuteReader();

                    while(dr.Read())
                    {
                        dataGridView1.Rows.Add(
                            dr["部品表ID"].ToString(),
                            dr["集計ID"].ToString(),
                            dr["部品ID"].ToString(),
                            dr["名称"].ToString(),
                            dr["材質"].ToString(),
                            dr["寸法"].ToString(),
                            dr["単価"].ToString(),
                            string.Format("{0:f2}", dr["数量"]), "");
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    InputClear();
                    return;
                }
                finally
                {
                    con.Close();
                }

                try
                {
                    decimal ManHour = 0;
                    decimal PaintingCost = 0;
                    decimal PartsCost1 = 0;
                    decimal PartsCost2 = 0;
                    decimal PartsCost3 = 0;
                    decimal YieldRatio = 0;

                    for (int k = 0; k < dataGridView1.Rows.Count; k++)
                    {
                        decimal UnitPrice = decimal.Parse(dataGridView1.Rows[k].Cells[6].Value.ToString());
                        decimal Quantity = decimal.Parse(dataGridView1.Rows[k].Cells[7].Value.ToString());

                        switch (dataGridView1.Rows[k].Cells[1].Value.ToString())
                        {
                            case "1":
                                ManHour += UnitPrice * Quantity;
                                break;
                            case "2":
                                PaintingCost += UnitPrice * Quantity;
                                break;
                            case "3":
                                PartsCost1 += UnitPrice * Quantity;
                                break;
                            case "4":
                                PartsCost2 += UnitPrice * Quantity;
                                break;
                            case "5":
                                PartsCost3 += UnitPrice * Quantity;
                                break;
                        }
                    }
                    textBox4.Text = string.Format("{0:f0}", ManHour * (decimal)4000);
                    textBox5.Text = string.Format("{0:f0}", PaintingCost);
                    textBox22.Text = string.Format("{0:f0}", PartsCost1);//鋼材(3)
                    textBox23.Text = string.Format("{0:f0}", PartsCost2);//購入品(4)
                    textBox25.Text = string.Format("{0:f0}", PartsCost3);//その他(5)
                    YieldRatio = PartsCost1 * (decimal)0.2;
                    textBox24.Text = string.Format("{0:f0}", YieldRatio);//歩留
                    textBox7.Text = string.Format("{0:f0}", PartsCost1 + PartsCost2 + PartsCost3 + YieldRatio);
                }
                catch
                {
                    MessageBox.Show("部品の単価・数量が不正な値です。修正してください。");
                }
                switch (sender)
                {
                    case "読込":
                    case "検索":
                        MessageBox.Show("取得しました。");
                        break;
                    case "削除":
                        MessageBox.Show("削除しました。");
                        break;
                    case "追加":
                        MessageBox.Show("追加しました。");
                        break;
                    case "新規":
                        MessageBox.Show("作成しました。");
                        break;
                    case "更新":
                        MessageBox.Show("更新しました。");
                        break;
                }

                isLoad = true;

                dataGridView1.Columns[0].ReadOnly = true;
                dataGridView1.Columns[1].ReadOnly = false;
                dataGridView1.Columns[2].ReadOnly = true;
                dataGridView1.Columns[3].ReadOnly = true;
                dataGridView1.Columns[4].ReadOnly = true;
                dataGridView1.Columns[5].ReadOnly = true;
                dataGridView1.Columns[6].ReadOnly = true;
                dataGridView1.Columns[7].ReadOnly = false;
                dataGridView1.Columns[8].ReadOnly = true;
                dataGridView1.Columns[8].ReadOnly = true;

                textBox1.ForeColor = Color.Blue;
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OleDb;

namespace 積算_原価管理

{

public partial class Form1 : Form

{

Boolean isLoad = false;

private List GetControls(string pattern)

{

List l = new List();

foreach (Control c in this.Controls)

{

if (pattern == "DecimalText")

{

switch (c.Name)

{

case "textBox14":

case "textBox15":

case "textBox6":

case "textBox8":

case "textBox9":

case "textBox10":

case "textBox11":

case "textBox12":

case "textBox13":

l.Add(c);

break;

}

else if(pattern == "ProductInputs")

{

switch (c.Name)

{

case "textBox1":

case "comboBox1":

case "comboBox2":

case "comboBox3":

case "textBox14":

case "textBox15":

case "textBox4":

case "textBox5":

case "textBox6":

case "textBox7":

case "textBox8":

case "textBox9":

case "textBox10":

case "textBox11":

case "textBox12":

case "textBox13":

case "textBox16":

l.Add(c);

break;

}

else if (pattern == "PartsInput")

{

switch (c.Name)

{

case "textBox2":

case "textBox18":

case "textBox19":

case "textBox20":

case "textBox21":

case "textBox3":

case "textBox17":

l.Add(c);

break;

}

return l;

}

private string BuildQuery(string pattern)

{

if (pattern == "新規")

{

return "INSERT INTO 製品 (向先,型式,製作先,販売価格,営業手配分,外注費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考) VALUES (" +

"'" + comboBox1.Text + "'," +

"'" + comboBox2.Text + "'," +

"'" + comboBox3.Text + "'," +

"'" + textBox14.Text + "'," +

"'" + textBox15.Text + "'," +

"'" + textBox6.Text + "'," +

"'" + textBox8.Text + "'," +

"'" + textBox9.Text + "'," +

"'" + textBox10.Text + "'," +

"'" + textBox11.Text + "'," +

"'" + textBox12.Text + "'," +

"'" + textBox13.Text + "'," +

"'" + textBox16.Text + "')";

}

else if (pattern == "更新")

{

return "UPDATE 製品 SET " +

"向先 = '" + comboBox1.Text + "'," +

"型式 = '" + comboBox2.Text + "'," +

"製作先 = '" + comboBox3.Text + "'," +

"販売価格 = '" + textBox14.Text + "'," +

"営業手配分 = '" + textBox15.Text + "'," +

"外注費 = '" + textBox6.Text + "'," +

"輸送費荷受 = '" + textBox8.Text + "'," +

"輸送費納品 = '" + textBox9.Text + "'," +

"試験費 = '" + textBox10.Text + "'," +

"品証費用 = '" + textBox11.Text + "'," +

"品証経費 = '" + textBox12.Text + "'," +

"社内製作分 = '" + textBox13.Text + "'," +

"備考 = '" + textBox16.Text + "' " +

"WHERE ID = " + textBox1.Text + "";

}

else if (pattern =="削除")

{

return "DELETE FROM 製品 WHERE ID = " + textBox1.Text + "";

}

return "";

}

private void InitGridView()

{

dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

dataGridView1.AllowUserToAddRows = false;

//dataGridView1.ReadOnly = true;

dataGridView1.Font = new Font("MS UI Gothic", 12);

dataGridView1.Columns.Add("部品表ID", "部品表ID");

dataGridView1.Columns.Add("集計ID", "集計ID");

dataGridView1.Columns.Add("部品ID", "部品ID");

dataGridView1.Columns.Add("名称", "名称");

dataGridView1.Columns.Add("材質", "材質");

dataGridView1.Columns.Add("寸法", "寸法");

dataGridView1.Columns.Add("単価", "単価");

dataGridView1.Columns.Add("数量", "数量");

DataGridViewButtonColumn col = new DataGridViewButtonColumn();

col.Name = "更新";

col.UseColumnTextForButtonValue = true;

col.Text = "更新";

dataGridView1.Columns.Add(col);

col = new DataGridViewButtonColumn();

col.Name = "削除";

col.UseColumnTextForButtonValue = true;

col.Text = "削除";

dataGridView1.Columns.Add(col);

dataGridView1.Columns["部品表ID"].Visible = false;

}

public Form1()

{

InitializeComponent();

LoadMaster();

InitGridView();

//読込

button1.Click += new EventHandler((object sender ,EventArgs e) => { LoadTable("読込"); });

//検索

button2.Click += new EventHandler((object sender, EventArgs e) => { LoadTable("検索"); });

//一覧

button8.Click += new EventHandler((object sender, EventArgs e) => {

Form f = new Form3(this);

f.StartPosition = FormStartPosition.Manual;

f.Location = new Point(Cursor.Position.X + 50, Cursor.Position.Y);

f.ShowDialog();

});

//新規

button7.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("新規"); });

//更新

button4.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("更新"); });

//削除

button6.Click += new EventHandler((object sender, EventArgs e) => { DataChanges("削除"); });

//部品ID検索

button5.Click += new EventHandler((object sender, EventArgs e) => {

Form f = new Form2(this);

f.StartPosition = FormStartPosition.Manual;

f.Location = new Point(Cursor.Position.X + 50,Cursor.Position.Y - 550);

f.ShowDialog();

});

//部品追加

button3.Click += new EventHandler(AddRow);

dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

textBox1.ForeColor = Color.Red;

textBox1.TextChanged += new EventHandler((object sender, EventArgs e) => {

isLoad = false;

textBox1.ForeColor = Color.Red;

});

}

private Boolean TryDecimal()

{

try

{

foreach (Control c in GetControls("DecimalText"))

{

if (c.Text == "") c.Text = "0";

Decimal.Parse(c.Text);

}

return true;

}

catch

{

return false;

}

private Boolean ProductsInputCheck(string sender)

{

if (sender == "新規")

{

if (textBox1.Text != "")

{

MessageBox.Show("製品IDが入力されています。");

return false;

}

if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")

{

MessageBox.Show("向先・型式・製作先が空白です");

return false;

}

if (!TryDecimal())

{

MessageBox.Show("金額が正しくありません。");

return false;

}

else if (sender == "更新")

{

if (textBox1.Text == "")

{

MessageBox.Show("製品IDが入力されていません。");

return false;

}

if (comboBox1.Text == "" || comboBox2.Text == "" || comboBox3.Text == "")

{

MessageBox.Show("向先・型式・製作先が空白です");

return false;

}

if (!isLoad)

{

MessageBox.Show("製品IDが正しくない可能性があります。");

return false;

}

if (!TryDecimal())

{

MessageBox.Show("金額が正しくありません。");

return false;

}

else if (sender == "削除")

{

if (textBox1.Text == "")

{

MessageBox.Show("製品IDが入力されていません。");

return false;

}

if (!isLoad)

{

MessageBox.Show("製品IDが正しくない可能性があります。");

return false;

}

return true;

}

private void DataChanges(string sender)

{

if (!ProductsInputCheck(sender)) return;

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(BuildQuery(sender), con);

cmd.ExecuteNonQuery();

if (sender == "新規")

{

cmd = new OleDbCommand("SELECT @@IDENTITY", con);

using (OleDbDataReader dr = cmd.ExecuteReader())

{

if (dr.Read()) textBox1.Text = dr[0].ToString();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

LoadMaster();

if (sender == "削除")

{

MessageBox.Show("削除しました。");

InputClear();

}

else

{

LoadTable(sender);

}

private void dgvClick(object sender, DataGridViewCellEventArgs e)

{

int col = e.ColumnIndex;

int row = e.RowIndex;

//削除

if (col == 9 && row >= 0)

{

if (!isLoad)

{

MessageBox.Show("製品IDが正しくない可能性があります。");

return;

}

string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品表 WHERE 部品表ID = " + id + "", con);

cmd.ExecuteNonQuery();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

LoadTable("削除");

}

//更新

if (col == 8 && row >= 0)

{

if (!isLoad)

{

MessageBox.Show("製品IDが正しくない可能性があります。");

return;

}

string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

string quantity = dataGridView1.Rows[row].Cells[7].Value.ToString();

string calculateId = dataGridView1.Rows[row].Cells[1].Value.ToString();

try

{

decimal.Parse(quantity);

int i = int.Parse(calculateId);

if (i > 5 || i < 1) throw new Exception(); } catch { MessageBox.Show("集計ID・数量が正しくありません。"); return; } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand( "UPDATE 部品表 SET 集計ID = " + calculateId + ", 数量 = " + quantity + " WHERE 部品表ID = " + id + "", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); InputClear(); return; } finally { con.Close(); } } LoadTable("更新"); } } private void InputClear() { dataGridView1.Rows.Clear(); foreach (Control c in GetControls("ProductInputs")) { c.Text = ""; } } private void LoadMaster() { comboBox1.Items.Clear(); comboBox2.Items.Clear(); comboBox3.Items.Clear(); using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd; OleDbDataReader dr; foreach (string str in new string[] {"向先","型式","製作先" }) { cmd = new OleDbCommand("SELECT DISTINCT " + str +" FROM 製品", con); dr = cmd.ExecuteReader(); while(dr.Read()) { if(str == "向先") this.comboBox1.Items.Add(dr["向先"].ToString()); if(str == "型式") this.comboBox2.Items.Add(dr["型式"].ToString()); if(str == "製作先") this.comboBox3.Items.Add(dr["製作先"].ToString()); } } } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } } private Boolean TryInt() { try { decimal.Parse(textBox17.Text); int i = int.Parse(textBox3.Text); if (i > 5 || i < 1) return false; } catch { return false; } return true; } private Boolean PartsInputCheck() { if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox17.Text == "") { MessageBox.Show("製品ID・部品ID・集計ID・数量を入力してください。"); return false; } if (!isLoad) { MessageBox.Show("製品IDが正しくない可能性があります。"); return false; } if (!TryInt()) { MessageBox.Show("集計ID・数量が正しくありません。"); return false; } return true; } private void AddRow(object sender, EventArgs e) { if (!PartsInputCheck()) return; using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品表 (製品ID,集計ID,部品ID_,数量) VALUES (" + textBox1.Text + "," + textBox3.Text + "," + textBox2.Text + "," + textBox17.Text + ")", con); cmd.ExecuteNonQuery(); } catch { MessageBox.Show("データベースに接続できませんでした。"); return; } finally { con.Close(); } } foreach (Control c in GetControls("PartsInput")) { c.Text = ""; } LoadTable("追加"); } private void LoadTable(string sender) { dataGridView1.Rows.Clear(); if(sender == "読込") { if (this.textBox1.Text == "") { MessageBox.Show("IDを入力してください。"); InputClear(); return; } } else if (sender == "検索") { if (this.comboBox1.Text == "" || this.comboBox2.Text == "" || this.comboBox3.Text == "") { MessageBox.Show("向先・型式・製作先を入力してください。"); InputClear(); return; } } using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb")) { try { con.Open(); string q = ""; switch (sender) { case "検索": case "新規": q = "SELECT * FROM 製品 WHERE 向先 = '" + comboBox1.Text + "' AND 型式 = '" + comboBox2.Text + "' AND 製作先 = '" + comboBox3.Text + "'"; break; case "読込": case "削除": case "追加": case "更新": q = "SELECT * FROM 製品 WHERE ID = " + this.textBox1.Text + ""; break; } OleDbCommand cmd = new OleDbCommand(q, con); OleDbDataReader dr = cmd.ExecuteReader(); if (!dr.Read()) { MessageBox.Show("データがありませんでした。"); InputClear(); return; } else { int i = 0; do { this.textBox1.Text = dr["ID"].ToString(); this.comboBox1.Text = dr["向先"].ToString(); this.comboBox2.Text = dr["型式"].ToString(); this.comboBox3.Text = dr["製作先"].ToString(); this.textBox14.Text = string.Format("{0:f0}", dr["販売価格"]); this.textBox15.Text = string.Format("{0:f0}", dr["営業手配分"]); this.textBox6.Text = string.Format("{0:f0}", dr["外注費"]); this.textBox8.Text = string.Format("{0:f0}", dr["輸送費荷受"]); this.textBox9.Text = string.Format("{0:f0}", dr["輸送費納品"]); this.textBox10.Text = string.Format("{0:f0}", dr["試験費"]); this.textBox11.Text = string.Format("{0:f0}", dr["品証費用"]); this.textBox12.Text = string.Format("{0:f0}", dr["品証経費"]); this.textBox13.Text = string.Format("{0:f0}", dr["社内製作分"]); this.textBox16.Text = dr["備考"].ToString(); i++; } while (dr.Read()); if (i > 1) MessageBox.Show("向先・型式・製作先の組合わせが重複しているので注意してください。");

}

cmd = new OleDbCommand("SELECT * FROM 部品表 LEFT OUTER JOIN 部品 ON 部品表.部品ID_ = 部品.部品ID WHERE 製品ID = " + this.textBox1.Text + "" , con);

dr = cmd.ExecuteReader();

while(dr.Read())

{

dataGridView1.Rows.Add(

dr["部品表ID"].ToString(),

dr["集計ID"].ToString(),

dr["部品ID"].ToString(),

dr["名称"].ToString(),

dr["材質"].ToString(),

dr["寸法"].ToString(),

dr["単価"].ToString(),

string.Format("{0:f2}", dr["数量"]), "");

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

InputClear();

return;

}

finally

{

con.Close();

}

try

{

decimal ManHour = 0;

decimal PaintingCost = 0;

decimal PartsCost1 = 0;

decimal PartsCost2 = 0;

decimal PartsCost3 = 0;

decimal YieldRatio = 0;

for (int k = 0; k < dataGridView1.Rows.Count; k++)

{

decimal UnitPrice = decimal.Parse(dataGridView1.Rows[k].Cells[6].Value.ToString());

decimal Quantity = decimal.Parse(dataGridView1.Rows[k].Cells[7].Value.ToString());

switch (dataGridView1.Rows[k].Cells[1].Value.ToString())

{

case "1":

ManHour += UnitPrice * Quantity;

break;

case "2":

PaintingCost += UnitPrice * Quantity;

break;

case "3":

PartsCost1 += UnitPrice * Quantity;

break;

case "4":

PartsCost2 += UnitPrice * Quantity;

break;

case "5":

PartsCost3 += UnitPrice * Quantity;

break;

}

textBox4.Text = string.Format("{0:f0}", ManHour * (decimal)4000);

textBox5.Text = string.Format("{0:f0}", PaintingCost);

textBox22.Text = string.Format("{0:f0}", PartsCost1);//鋼材(3)

textBox23.Text = string.Format("{0:f0}", PartsCost2);//購入品(4)

textBox25.Text = string.Format("{0:f0}", PartsCost3);//その他(5)

YieldRatio = PartsCost1 * (decimal)0.2;

textBox24.Text = string.Format("{0:f0}", YieldRatio);//歩留

textBox7.Text = string.Format("{0:f0}", PartsCost1 + PartsCost2 + PartsCost3 + YieldRatio);

}

catch

{

MessageBox.Show("部品の単価・数量が不正な値です。修正してください。");

}

switch (sender)

{

case "読込":

case "検索":

MessageBox.Show("取得しました。");

break;

case "削除":

MessageBox.Show("削除しました。");

break;

case "追加":

MessageBox.Show("追加しました。");

break;

case "新規":

MessageBox.Show("作成しました。");

break;

case "更新":

MessageBox.Show("更新しました。");

break;

}

isLoad = true;

dataGridView1.Columns[0].ReadOnly = true;

dataGridView1.Columns[1].ReadOnly = false;

dataGridView1.Columns[2].ReadOnly = true;

dataGridView1.Columns[3].ReadOnly = true;

dataGridView1.Columns[4].ReadOnly = true;

dataGridView1.Columns[5].ReadOnly = true;

dataGridView1.Columns[6].ReadOnly = true;

dataGridView1.Columns[7].ReadOnly = false;

dataGridView1.Columns[8].ReadOnly = true;

textBox1.ForeColor = Color.Blue;

}

Form2.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace 積算_原価管理
{
    public partial class Form2 : Form
    {
        Form f1;
        public Form2(Form f)
        {
            InitializeComponent();

            f1 = f;

            button1.Click += new EventHandler(FindRows);
            button3.Click += new EventHandler(addRow);

            this.KeyPreview = true;
            this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {
                if (e.KeyCode == Keys.Escape) this.Close();
            });

            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.Font = new Font("MS UI Gothic", 12);

            dataGridView1.Columns.Add("部品ID", "部品ID");
            dataGridView1.Columns["部品ID"].ReadOnly = true;

            dataGridView1.Columns.Add("名称", "名称");
            dataGridView1.Columns.Add("材質", "材質");
            dataGridView1.Columns.Add("寸法", "寸法");
            dataGridView1.Columns.Add("単価", "単価");

            DataGridViewButtonColumn col = new DataGridViewButtonColumn();
            col.Name = "更新";
            col.UseColumnTextForButtonValue = true;
            col.Text = "更新";
            dataGridView1.Columns.Add(col);

            col = new DataGridViewButtonColumn();
            col.Name = "削除";
            col.UseColumnTextForButtonValue = true;
            col.Text = "削除";
            dataGridView1.Columns.Add(col);

            LoadMaster();
        }

        private void dgvClick(object sender, DataGridViewCellEventArgs e)
        {
            int col = e.ColumnIndex;
            int row = e.RowIndex;

            if(col == -1 && row >= 0)
            {
                f1.Controls["textBox2"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();
                f1.Controls["textBox18"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();
                f1.Controls["textBox19"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();
                f1.Controls["textBox20"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();
                f1.Controls["textBox21"].Text = dataGridView1.Rows[row].Cells[4].Value.ToString();
                f1.Controls["textBox3"].Text = "";
                f1.Controls["textBox17"].Text = "";
            }

            if (col == 5 && row >= 0)
            {
                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();
                string name = dataGridView1.Rows[row].Cells[1].Value.ToString();
                string material = dataGridView1.Rows[row].Cells[2].Value.ToString();
                string size = dataGridView1.Rows[row].Cells[3].Value.ToString();
                string price = dataGridView1.Rows[row].Cells[4].Value.ToString();

                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
                {
                    try
                    {
                        con.Open();

                        OleDbCommand cmd = new OleDbCommand("UPDATE 部品 SET " + 
                            "名称 = '" + name + "', 材質 = '" + material + "', 寸法 = '" + size + "', 単価 = " + price + " WHERE 部品ID = " + id + "", con);
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        MessageBox.Show("データベースに接続できませんでした。");
                        return;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                LoadMaster();
                BaseFormClear();
                MessageBox.Show("更新しました。");
            }

            if (col == 6 && row >= 0)
            {
                string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

                using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
                {
                    try
                    {
                        con.Open();

                        OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品 WHERE 部品ID = " + id + "", con);
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        MessageBox.Show("データベースに接続できませんでした。");
                        return;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
                LoadMaster();
                BaseFormClear();
                MessageBox.Show("削除しました。");
            }
        }
        private void BaseFormClear()
        {
            foreach (Control c in f1.Controls)
            {
                if (c is TextBox) c.Text = "";
                if (c is ComboBox) c.Text = "";
            }
            ((DataGridView)f1.Controls["dataGridView1"]).Rows.Clear();
        }

        private void FindRows(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                LoadMaster();
            }
            else
            {
                LoadMaster("SELECT * FROM 部品 WHERE 名称 LIKE '%" + textBox1.Text + "%' OR 材質 LIKE '%" + textBox1.Text + "%'");
                textBox1.Text = "";
            }
        }

        private void addRow(object sender, EventArgs e)
        {
            if(textBox18.Text == "" || textBox4.Text == "")
            {
                MessageBox.Show("名称・単価を入力してください。");
                LoadMaster();
                return;
            }

            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
            {
                try
                {
                    con.Open();

                    OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品 (名称,材質,寸法,単価) VALUES ('" + 
                        textBox18.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')", con);
                    cmd.ExecuteNonQuery();

                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    return;
                }
                finally
                {
                    con.Close();
                }
            }
            this.textBox18.Text = "";
            this.textBox2.Text = "";
            this.textBox3.Text = "";
            this.textBox4.Text = "";

            LoadMaster();
            MessageBox.Show("追加しました。");            
        }

        private void LoadMaster(string q = "SELECT * FROM 部品")
        {
            f1.Controls["textBox2"].Text = "";
            f1.Controls["textBox18"].Text = "";
            f1.Controls["textBox19"].Text = "";
            f1.Controls["textBox20"].Text = ""; 
            f1.Controls["textBox21"].Text = "";

            dataGridView1.Rows.Clear();

            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
            {
                try
                {
                    con.Open();

                    OleDbCommand cmd = new OleDbCommand(q, con);
                    OleDbDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        this.dataGridView1.Rows.Add(
                            dr["部品ID"].ToString(),
                            dr["名称"].ToString(),
                            dr["材質"].ToString(),
                            dr["寸法"].ToString(),
                            dr["単価"].ToString(),"","");
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    return;
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OleDb;

namespace 積算_原価管理

{

public partial class Form2 : Form

{

Form f1;

public Form2(Form f)

{

InitializeComponent();

f1 = f;

button1.Click += new EventHandler(FindRows);

button3.Click += new EventHandler(addRow);

this.KeyPreview = true;

this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {

if (e.KeyCode == Keys.Escape) this.Close();

});

dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

dataGridView1.AllowUserToAddRows = false;

dataGridView1.Font = new Font("MS UI Gothic", 12);

dataGridView1.Columns.Add("部品ID", "部品ID");

dataGridView1.Columns["部品ID"].ReadOnly = true;

dataGridView1.Columns.Add("名称", "名称");

dataGridView1.Columns.Add("材質", "材質");

dataGridView1.Columns.Add("寸法", "寸法");

dataGridView1.Columns.Add("単価", "単価");

DataGridViewButtonColumn col = new DataGridViewButtonColumn();

col.Name = "更新";

col.UseColumnTextForButtonValue = true;

col.Text = "更新";

dataGridView1.Columns.Add(col);

col = new DataGridViewButtonColumn();

col.Name = "削除";

col.UseColumnTextForButtonValue = true;

col.Text = "削除";

dataGridView1.Columns.Add(col);

LoadMaster();

}

private void dgvClick(object sender, DataGridViewCellEventArgs e)

{

int col = e.ColumnIndex;

int row = e.RowIndex;

if(col == -1 && row >= 0)

{

f1.Controls["textBox2"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();

f1.Controls["textBox18"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();

f1.Controls["textBox19"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();

f1.Controls["textBox20"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();

f1.Controls["textBox21"].Text = dataGridView1.Rows[row].Cells[4].Value.ToString();

f1.Controls["textBox3"].Text = "";

f1.Controls["textBox17"].Text = "";

}

if (col == 5 && row >= 0)

{

string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

string name = dataGridView1.Rows[row].Cells[1].Value.ToString();

string material = dataGridView1.Rows[row].Cells[2].Value.ToString();

string size = dataGridView1.Rows[row].Cells[3].Value.ToString();

string price = dataGridView1.Rows[row].Cells[4].Value.ToString();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand("UPDATE 部品 SET " +

"名称 = '" + name + "', 材質 = '" + material + "', 寸法 = '" + size + "', 単価 = " + price + " WHERE 部品ID = " + id + "", con);

cmd.ExecuteNonQuery();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

LoadMaster();

BaseFormClear();

MessageBox.Show("更新しました。");

}

if (col == 6 && row >= 0)

{

string id = dataGridView1.Rows[row].Cells[0].Value.ToString();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand("DELETE FROM 部品 WHERE 部品ID = " + id + "", con);

cmd.ExecuteNonQuery();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

LoadMaster();

BaseFormClear();

MessageBox.Show("削除しました。");

}

private void BaseFormClear()

{

foreach (Control c in f1.Controls)

{

if (c is TextBox) c.Text = "";

if (c is ComboBox) c.Text = "";

}

((DataGridView)f1.Controls["dataGridView1"]).Rows.Clear();

}

private void FindRows(object sender, EventArgs e)

{

if (textBox1.Text == "")

{

LoadMaster();

}

else

{

LoadMaster("SELECT * FROM 部品 WHERE 名称 LIKE '%" + textBox1.Text + "%' OR 材質 LIKE '%" + textBox1.Text + "%'");

textBox1.Text = "";

}

private void addRow(object sender, EventArgs e)

{

if(textBox18.Text == "" || textBox4.Text == "")

{

MessageBox.Show("名称・単価を入力してください。");

LoadMaster();

return;

}

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand("INSERT INTO 部品 (名称,材質,寸法,単価) VALUES ('" +

textBox18.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')", con);

cmd.ExecuteNonQuery();

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

this.textBox18.Text = "";

this.textBox2.Text = "";

this.textBox3.Text = "";

this.textBox4.Text = "";

LoadMaster();

MessageBox.Show("追加しました。");

}

private void LoadMaster(string q = "SELECT * FROM 部品")

{

f1.Controls["textBox2"].Text = "";

f1.Controls["textBox18"].Text = "";

f1.Controls["textBox19"].Text = "";

f1.Controls["textBox20"].Text = "";

f1.Controls["textBox21"].Text = "";

dataGridView1.Rows.Clear();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(q, con);

OleDbDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

this.dataGridView1.Rows.Add(

dr["部品ID"].ToString(),

dr["名称"].ToString(),

dr["材質"].ToString(),

dr["寸法"].ToString(),

dr["単価"].ToString(),"","");

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

Form3.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using System.Data.OleDb;

namespace 積算_原価管理
{
    public partial class Form3 : Form
    {
        Form f1;
        public Form3(Form f)
        {
            InitializeComponent();

            f1 = f;

            button1.Click += new EventHandler(FindRows);

            this.KeyPreview = true;
            this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {
                if (e.KeyCode == Keys.Escape) this.Close();
            });

            dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            dataGridView1.AllowUserToAddRows = false;

            dataGridView1.Font = new Font("MS UI Gothic", 12);
            dataGridView1.ReadOnly = true;

            dataGridView1.Columns.Add("ID", "ID");
            dataGridView1.Columns.Add("向先", "向先");
            dataGridView1.Columns.Add("型式", "型式");
            dataGridView1.Columns.Add("製作先", "製作先");

            LoadMaster();
        }

        private void dgvClick(object sender, DataGridViewCellEventArgs e)
        {
            int col = e.ColumnIndex;
            int row = e.RowIndex;

            if (col == -1 && row >= 0)
            {
                f1.Controls["textBox1"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();
                f1.Controls["comboBox1"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();
                f1.Controls["comboBox2"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();
                f1.Controls["comboBox3"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();
            }
        }

        private void FindRows(object sender, EventArgs e)
        {
            if (textBox1.Text == "")
            {
                LoadMaster();
            }
            else
            {
                LoadMaster("SELECT ID,向先,型式,製作先 FROM 製品 WHERE 向先 LIKE '%" + textBox1.Text + "%' OR 型式 LIKE '%" + textBox1.Text + "%' OR 製作先 LIKE '%" + textBox1.Text + "%'");
                textBox1.Text = "";
            }
        }

        private void LoadMaster(string q = "SELECT ID,向先,型式,製作先 FROM 製品")
        {
            f1.Controls["textBox1"].Text = "";
            f1.Controls["comboBox1"].Text = "";
            f1.Controls["comboBox2"].Text = "";
            f1.Controls["comboBox3"].Text = "";

            dataGridView1.Rows.Clear();

            using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))
            {
                try
                {
                    con.Open();

                    OleDbCommand cmd = new OleDbCommand(q, con);
                    OleDbDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        this.dataGridView1.Rows.Add(dr["ID"].ToString(),dr["向先"].ToString(),dr["型式"].ToString(), dr["製作先"].ToString());
                    }
                }
                catch
                {
                    MessageBox.Show("データベースに接続できませんでした。");
                    return;
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.OleDb;

namespace 積算_原価管理

{

public partial class Form3 : Form

{

Form f1;

public Form3(Form f)

{

InitializeComponent();

f1 = f;

button1.Click += new EventHandler(FindRows);

this.KeyPreview = true;

this.KeyDown += new KeyEventHandler((object sender, KeyEventArgs e) => {

if (e.KeyCode == Keys.Escape) this.Close();

});

dataGridView1.CellClick += new DataGridViewCellEventHandler(dgvClick);

dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;

dataGridView1.AllowUserToAddRows = false;

dataGridView1.Font = new Font("MS UI Gothic", 12);

dataGridView1.ReadOnly = true;

dataGridView1.Columns.Add("ID", "ID");

dataGridView1.Columns.Add("向先", "向先");

dataGridView1.Columns.Add("型式", "型式");

dataGridView1.Columns.Add("製作先", "製作先");

LoadMaster();

}

private void dgvClick(object sender, DataGridViewCellEventArgs e)

{

int col = e.ColumnIndex;

int row = e.RowIndex;

if (col == -1 && row >= 0)

{

f1.Controls["textBox1"].Text = dataGridView1.Rows[row].Cells[0].Value.ToString();

f1.Controls["comboBox1"].Text = dataGridView1.Rows[row].Cells[1].Value.ToString();

f1.Controls["comboBox2"].Text = dataGridView1.Rows[row].Cells[2].Value.ToString();

f1.Controls["comboBox3"].Text = dataGridView1.Rows[row].Cells[3].Value.ToString();

}

private void FindRows(object sender, EventArgs e)

{

if (textBox1.Text == "")

{

LoadMaster();

}

else

{

LoadMaster("SELECT ID,向先,型式,製作先 FROM 製品 WHERE 向先 LIKE '%" + textBox1.Text + "%' OR 型式 LIKE '%" + textBox1.Text + "%' OR 製作先 LIKE '%" + textBox1.Text + "%'");

textBox1.Text = "";

}

private void LoadMaster(string q = "SELECT ID,向先,型式,製作先 FROM 製品")

{

f1.Controls["textBox1"].Text = "";

f1.Controls["comboBox1"].Text = "";

f1.Controls["comboBox2"].Text = "";

f1.Controls["comboBox3"].Text = "";

dataGridView1.Rows.Clear();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\genka_db_2.mdb"))

{

try

{

con.Open();

OleDbCommand cmd = new OleDbCommand(q, con);

OleDbDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

this.dataGridView1.Rows.Add(dr["ID"].ToString(),dr["向先"].ToString(),dr["型式"].ToString(), dr["製作先"].ToString());

}

catch

{

MessageBox.Show("データベースに接続できませんでした。");

return;

}

finally

{

con.Close();

}

取り出しはVBAにて

Sub func_down()

On Error GoTo e

ym = MsgBox("一覧表で実行する必要があります。" & Chr(13) & "実行しますか？", vbYesNo)
If ym = vbNo Then Exit Sub

Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

cn.Provider = "Microsoft.JET.OLEDB.4.0"
cn.Properties("Data Source").Value = ThisWorkbook.Path & "\genka_db_2.mdb"
cn.Open
cn.CursorLocation = 3

c = 1

With ActiveSheet

For r = 7 To .UsedRange.Rows.Count

  Customer = .Cells(r, c + 1).Value
  ProductName = .Cells(r, c + 2).Value
  Supplier = .Cells(r, c + 3).Value
  
  If Customer <> "" And ProductName <> "" Then
  
    q = "SELECT ID,向先,型式,販売価格,営業手配分,工数,塗装費,外注費,材料費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考 " & _
    "FROM 製品 WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "'"
    Debug.Print q
    
    rs.Open q, cn

    If rs.RecordCount = 1 Then

      .Cells(r, c + 4).Value = rs!販売価格
      .Cells(r, c + 5).Value = rs!営業手配分
      
      .Cells(r, c + 11).Value = rs!外注費
      .Cells(r, c + 13).Value = rs!輸送費荷受
      .Cells(r, c + 14).Value = rs!輸送費納品
      .Cells(r, c + 15).Value = rs!試験費
      .Cells(r, c + 21).Value = rs!品証費用
      .Cells(r, c + 22).Value = rs!品証経費
      .Cells(r, c + 23).Value = rs!社内製作分
      .Cells(r, c + 33).Value = rs!備考
      If rs.State = 1 Then rs.Close
      
      '工数
      
      Dim manHour As Currency: manHour = 0

      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 1 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      If rs.RecordCount = 1 Then manHour = rs!合計
      If rs.State = 1 Then rs.Close
      
      .Cells(r, c + 6).Value = manHour
      
      '塗装費
      
      Dim paintingCost As Currency: paintingCost = 0
      
      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 2 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      If rs.RecordCount = 1 Then paintingCost = rs!合計
      If rs.State = 1 Then rs.Close
      
      .Cells(r, c + 9).Value = paintingCost
      
      '材料費1
      
      Dim partsCost1 As Currency: partsCost1 = 0
      Dim partsCost2 As Currency: partsCost2 = 0
      Dim partsCost3 As Currency: partsCost3 = 0
      
      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 3 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      
      If rs.RecordCount = 1 Then partsCost1 = rs!合計
      If rs.State = 1 Then rs.Close
      
      '材料費2
      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 4 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      
      If rs.RecordCount = 1 Then partsCost2 = rs!合計
      If rs.State = 1 Then rs.Close
      
      '材料費3
      q = "SELECT " & _
      "部品表.集計ID, " & _
      "Sum(部品表.数量 * 部品.単価) AS 合計 " & _
      "FROM 部品表 LEFT JOIN 部品 " & _
      "ON 部品表.部品ID_ = 部品.部品ID " & _
      "WHERE (((部品表.[製品ID])=(SELECT ID " & _
      "FROM 製品 " & _
      "WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _
      "))) AND 集計ID = " & 5 & " " & _
      "GROUP BY 部品表.集計ID;"
      rs.Open q, cn
      
      If rs.RecordCount = 1 Then partsCost3 = rs!合計
      If rs.State = 1 Then rs.Close

      .Cells(r, c + 12).Value = partsCost1 + (partsCost1 * 0.2) + partsCost2 + partsCost3
    
    ElseIf rs.RecordCount > 1 Then
    
        MsgBox "向先・型式が重複している可能性があります。" & Chr(13) & r & "行目"
    
    End If
    
    If rs.State = 1 Then rs.Close
    
  End If

Next r

End With

If rs.State = 1 Then rs.Close
Set rs = Nothing

If cn.State = 1 Then cn.Close
Set cn = Nothing

MsgBox "終了しました。"

Exit Sub

e:

  If rs.State = 1 Then rs.Close
  Set rs = Nothing
  
  If cn.State = 1 Then cn.Close
  Set cn = Nothing
  
  MsgBox "不具合が発生しています。管理者へ連絡してください。"

End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

Sub func_down()

On Error GoTo e

ym = MsgBox("一覧表で実行する必要があります。" & Chr(13) & "実行しますか？", vbYesNo)

If ym = vbNo Then Exit Sub

Dim cn As Object

Set cn = CreateObject("ADODB.Connection")

Dim rs As Object

Set rs = CreateObject("ADODB.Recordset")

cn.Provider = "Microsoft.JET.OLEDB.4.0"

cn.Properties("Data Source").Value = ThisWorkbook.Path & "\genka_db_2.mdb"

cn.Open

cn.CursorLocation = 3

c = 1

With ActiveSheet

For r = 7 To .UsedRange.Rows.Count

Customer = .Cells(r, c + 1).Value

ProductName = .Cells(r, c + 2).Value

Supplier = .Cells(r, c + 3).Value

If Customer <> "" And ProductName <> "" Then

q = "SELECT ID,向先,型式,販売価格,営業手配分,工数,塗装費,外注費,材料費,輸送費荷受,輸送費納品,試験費,品証費用,品証経費,社内製作分,備考 " & _

"FROM 製品 WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "'"

Debug.Print q

rs.Open q, cn

If rs.RecordCount = 1 Then

.Cells(r, c + 4).Value = rs!販売価格

.Cells(r, c + 5).Value = rs!営業手配分

.Cells(r, c + 11).Value = rs!外注費

.Cells(r, c + 13).Value = rs!輸送費荷受

.Cells(r, c + 14).Value = rs!輸送費納品

.Cells(r, c + 15).Value = rs!試験費

.Cells(r, c + 21).Value = rs!品証費用

.Cells(r, c + 22).Value = rs!品証経費

.Cells(r, c + 23).Value = rs!社内製作分

.Cells(r, c + 33).Value = rs!備考

If rs.State = 1 Then rs.Close

'工数

Dim manHour As Currency: manHour = 0

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 1 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then manHour = rs!合計

If rs.State = 1 Then rs.Close

.Cells(r, c + 6).Value = manHour

'塗装費

Dim paintingCost As Currency: paintingCost = 0

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 2 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then paintingCost = rs!合計

If rs.State = 1 Then rs.Close

.Cells(r, c + 9).Value = paintingCost

'材料費1

Dim partsCost1 As Currency: partsCost1 = 0

Dim partsCost2 As Currency: partsCost2 = 0

Dim partsCost3 As Currency: partsCost3 = 0

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 3 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then partsCost1 = rs!合計

If rs.State = 1 Then rs.Close

'材料費2

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 4 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then partsCost2 = rs!合計

If rs.State = 1 Then rs.Close

'材料費3

q = "SELECT " & _

"部品表.集計ID, " & _

"Sum(部品表.数量 * 部品.単価) AS 合計 " & _

"FROM 部品表 LEFT JOIN 部品 " & _

"ON 部品表.部品ID_ = 部品.部品ID " & _

"WHERE (((部品表.[製品ID])=(SELECT ID " & _

"FROM 製品 " & _

"WHERE 向先 = '" & Customer & "' AND 型式 = '" & ProductName & "' AND 製作先 = '" & Supplier & "' " & _

"))) AND 集計ID = " & 5 & " " & _

"GROUP BY 部品表.集計ID;"

rs.Open q, cn

If rs.RecordCount = 1 Then partsCost3 = rs!合計

If rs.State = 1 Then rs.Close

.Cells(r, c + 12).Value = partsCost1 + (partsCost1 * 0.2) + partsCost2 + partsCost3

ElseIf rs.RecordCount > 1 Then

MsgBox "向先・型式が重複している可能性があります。" & Chr(13) & r & "行目"

End If

If rs.State = 1 Then rs.Close

End If

Next r

End With

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "終了しました。"

Exit Sub

If rs.State = 1 Then rs.Close

Set rs = Nothing

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "不具合が発生しています。管理者へ連絡してください。"

End Sub

即席登録

Private Sub CommandButton1_Click()

On Error GoTo e

Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

cn.Provider = "Microsoft.JET.OLEDB.4.0"
cn.Properties("Data Source").Value = ThisWorkbook.Path & "\genka_db_2.mdb"
cn.Open

With ActiveSheet

r = 2
Do While .Cells(r, 1) <> ""

  q = "INSERT INTO 部品 (名称,材質,寸法,単価) VALUES ('" & _
  .Cells(r, 1).Value & "','" & .Cells(r, 2).Value & "','" & .Cells(r, 3).Value & "','" & .Cells(r, 4).Value & "')"
  cn.Execute q: Debug.Print q
  
r = r + 1
Loop

End With

If cn.State = 1 Then cn.Close
Set cn = Nothing

MsgBox "終了しました。"

Exit Sub

e:

  If cn.State = 1 Then cn.Close
  Set cn = Nothing

  MsgBox "不具合が発生しています。"
  
End Sub

Private Sub CommandButton1_Click()

On Error GoTo e

Dim cn As Object

Set cn = CreateObject("ADODB.Connection")

cn.Provider = "Microsoft.JET.OLEDB.4.0"

cn.Properties("Data Source").Value = ThisWorkbook.Path & "\genka_db_2.mdb"

cn.Open

With ActiveSheet

r = 2

Do While .Cells(r, 1) <> ""

q = "INSERT INTO 部品 (名称,材質,寸法,単価) VALUES ('" & _

.Cells(r, 1).Value & "','" & .Cells(r, 2).Value & "','" & .Cells(r, 3).Value & "','" & .Cells(r, 4).Value & "')"

cn.Execute q: Debug.Print q

r = r + 1

Loop

End With

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "終了しました。"

Exit Sub

If cn.State = 1 Then cn.Close

Set cn = Nothing

MsgBox "不具合が発生しています。"

End Sub